Календари для Power Bi + замена часового пояса в Power Bi

Календарь для Power Bi

Полезные ссылки :

статья по Сортировке столбцов с датами и с информацией - Хитрости сортировки в Power BI.

______________________________

Ctrl+C для Power Bi

в справочник "Календарь"

Код для копирования:
заменяем выделенное жирным на столбцы с мин и макс датой в БД 

Calendar = ADDCOLUMNS(CALENDAR(MIN('Отчет'[min_date]), MAX('orders'[max_date])), 
    "Год", YEAR([Date]),
    "Квартал", QUARTER([Date]),
    "№ Месяца", MONTH([Date]),
    "Месяц", FORMAT([Date],"MMMM"),
    "Месяц - Год", FORMAT([Date],"mmm.yyyy"),
    "Начало Недели", [Date] +1 - WEEKDAY([Date], 2), -- столбец для сортировки "Начало-Конец Недели"
    "Начало-Конец Недели", FORMAT([Date] +1 - WEEKDAY([Date], 2), "dd.mm") & "-" & FORMAT([Date] +1 - WEEKDAY([Date], 2) + 6, "dd.mm"), -- расшифровка есть ниже
    "№ Дня недели", WEEKDAY([Date], 2), -- 2-русская неделя (пн-вс), для амер недели (вс-сб) заменить двойку на 1
    "День недели", FORMAT([Date], "dddd"),
    "День месяца", DAY([Date])
)

Для сортировки в полученной таблице выделяем столбец Месяц и в верхнем меню на вкладке "Средства работы со столбцами" нажимаем "Сортировать по столбцу", где выбираем № Месяца.
То же делаем для Начало-Конец Недели и День недели. Если этого не сделать, то в дашбордах Power Bi столбец Месяц будет отсортирован по алфавиту, а не по реальной очередности месяцев в году.
Потом справа в меню "Данные" в нашем получившемся Справочнике через троеточие создаем Иерархию Год > Квартал > Месяц - Год > Начало-Конец Недели > День месяца.

для вычисляемого столбца в таблицу

- ранжированный столбец

с часами от 0:00 до 23:59

! Ranged_24_hours = 
VAR Hours_24 = HOUR('wb_orders'[date])
VAR Ranged_24_hours = 
    IF(
        Hours_24 > 9, Hours_24 & ":00-" & Hours_24 & ":59",
        "0" & Hours_24 & ":00-0" & Hours_24 & ":59"
    )
RETURN
    Ranged_24_hours

______________________________

Ctrl+C через Power Query

5 простых шагов:

Перейти в Power Query
Cоздать Пустой запрос
Открыть Расширенный редактор
Вставить код
(1) заменить путь к колонкам на актуальный (без 'кавычек' в именах таблиц) или (2) руками проставить даты
Закрыть и применить
Отключить автоматическое создание иерархии дат в Power Bi Desktop
Создать вручную иерархию дат в Power Bi

Код для копирования:

заменяем выделенное жирным на столбцы с мин и макс датой в БД

let
    Source = List.Dates(DateTime.Date(List.Min(Отчет[min_date])), Duration.Days((List.Max(Отчет[max_date])) - (List.Min(Отчет[min_date]))) + 1, #duration(-1,0,0,0)),
    #"Converted to Table" = Table.FromList(calendar, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Дата"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Дата", type date}}),
    #"Inserted Год" =  Table.AddColumn(#"Changed Type", "Год", each Date.Year([Дата]), Int64.Type),
    #"Inserted Quarter of Year" = Table.AddColumn(#"Inserted Год", "Квартал", each Date.QuarterOfYear([Дата]), Int64.Type),
    #"Inserted Месяц" = Table.AddColumn(#"Inserted Quarter of Year", "Месяц", each Date.MonthName([Дата]), type text),
    #"Inserted Month and Year" = Table.AddColumn(#"Inserted Месяц", "Month and Year", each Text.Combine({Text.Start(Text.From([Месяц]), 3), ".", Text.End(Text.From([Год]), 2), "г"}), type text),
    #"Inserted Month №" = Table.AddColumn(#"Inserted Month and Year", "Month №", each Date.Month([Дата]), Int64.Type),
    #"Inserted Start of Month" = Table.AddColumn(#"Inserted Month №", "Start of Month", each Date.StartOfMonth([Дата]), type date),
    #"Inserted Start of Week" = Table.AddColumn(#"Inserted Start of Month", "Start of Week", each Date.StartOfWeek([Дата]), type date),
    #"Inserted End of Week" = Table.AddColumn(#"Inserted Start of Week", "End of Week", each Date.EndOfWeek([Дата]), type date),  
    #"Inserted Неделя" = Table.AddColumn(#"Inserted End of Week", "Неделя", each Text.Combine({Text.Start(Text.From([Start of Week]), 5), "-", Text.Start(Text.From([End of Week]), 5)}), type text),
    #"Inserted Week №" = Table.AddColumn(#"Inserted Неделя", "Week №", each Date.WeekOfYear([Дата]), Int64.Type),
    #"For Index" = Table.AddIndexColumn(#"Inserted Week №", "For Index Column", 1, 1, Int64.Type)
in
    #"For Index"

А это ручной ввод даты начала и до "сегодня" (когда еще не знаешь колонки с мин и макс датой в БД:

заменяем выделенное жирным на нужную дату в прошлом

let
    start_day = #date(2000, 1, 1),
    calendar = List.Dates(start_day, Number.From(DateTime.LocalNow() - #datetime(2000, 1, 1, 0, 0, 0)), #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(calendar, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Дата"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Дата", type date}}),
    #"Inserted Год" =  Table.AddColumn(#"Changed Type", "Год", each Date.Year([Дата]), Int64.Type),
    #"Inserted Quarter of Year" = Table.AddColumn(#"Inserted Год", "Квартал", each Date.QuarterOfYear([Дата]), Int64.Type),
    #"Inserted Месяц" = Table.AddColumn(#"Inserted Quarter of Year", "Месяц", each Date.MonthName([Дата]), type text),
    #"Inserted Month and Year" = Table.AddColumn(#"Inserted Месяц", "Month and Year", each Text.Combine({Text.Start(Text.From([Месяц]), 3), ".", Text.End(Text.From([Год]), 2), "г"}), type text),
    #"Inserted Month №" = Table.AddColumn(#"Inserted Month and Year", "Month №", each Date.Month([Дата]), Int64.Type),
    #"Inserted Start of Month" = Table.AddColumn(#"Inserted Month №", "Start of Month", each Date.StartOfMonth([Дата]), type date),
    #"Inserted Start of Week" = Table.AddColumn(#"Inserted Start of Month", "Start of Week", each Date.StartOfWeek([Дата]), type date),
    #"Inserted End of Week" = Table.AddColumn(#"Inserted Start of Week", "End of Week", each Date.EndOfWeek([Дата]), type date),  
    #"Inserted Неделя" = Table.AddColumn(#"Inserted End of Week", "Неделя", each Text.Combine({Text.Start(Text.From([Start of Week]), 5), "-", Text.Start(Text.From([End of Week]), 6), Text.End(Text.From([End of Week]), 2)}), type text),
    #"Inserted Week №" = Table.AddColumn(#"Inserted Неделя", "Week №", each Date.WeekOfYear([Дата]), Int64.Type),
    #"For Index" = Table.AddIndexColumn(#"Inserted Week №", "For Index Column", 1, 1, Int64.Type)
in
    #"For Index"

______________________________

отключение автоматической иерархии дат в Power Bi

Отключение автоматической иерархии дат очень желательно для избежания ошибок - при автом. создании иерархии создается отдельная таблица, которая связана с календарем (столбец Date) однонаправленной связью один ко многим. Не все фильтры по Date будут работать.

идем в Настройки Power Bi Desktop (версия окт.2023г.):

1й шаг).

Глобальные

Загрузка данных

Логика операций со временем (Time Intelligence) >> снимаем галочку с "Автоматические дата и время для новых файлов"

2й шаг).

Текущий файл

Загрузка данных

Логика операций со временем (Time Intelligence) >> снимаем галочку с "Автоматические дата и время"

____

о вреде автоматической иерархии в Power BI короткое понятное видео

Что скрывают иерархии дат Power BI и почему не работают простые фильтры

______________________________

Начало и Конец недели в DAX

StartOfWeek & EndOfWeek

Ищем начало недели. В каждой неделе 7 дней.

В DAX уже есть формула WEEKDAY('Data'[Date], которая выводит номер дня в неделе.

Соответственно для нахождения даты начала недели (Start Of Week) исходим из того, что от даты вычитаем номер дня недели. Компенсируем через "+ 1" необходимую разницу.

StartOfWeek = 'Data'[Date] +1 - WEEKDAY('Data'[Date], 2)

Теперь для нахождения даты конца недели (End Of Week) просто добавляем 6 дней к полученному StartOfWeek.

______________________________

Разбивка суток на 24 часа в DAX

00:59 - 00:59 ... 23:59 - 23:59

! 0-23 hours = 
VAR Hours_24 = HOUR('Data'[date])
VAR Ranged_24_hours = 
    IF(
        Hours_24 > 9, Hours_24 & ":00-" & Hours_24 & ":59",
        "0" & Hours_24 & ":00-0" & Hours_24 & ":59"
    )
RETURN
    Ranged_24_hours

Быстрая СМЕНА ЧАСОВОГО ПОЯСА в Power Query

Самый способ преобразования в местный часовой пояс в Power BI / Power Query

перейти на вкладку «Преобразование» или на вкладку «Добавить столбец» и
в группе «Дата и время». выберите вариант местного времени, и вы увидите в раскрывающемся списке вариант для местного времени - это время вашег компьютера.

полезная ссылка по смене часового пояса через SWITCH