Power BI
Aplikace ve finančním reportingu

 

Zdrojová data

Pozadí reportu

Funkce na generování datumové tabulky

let
CreateDateTable = (StartDate as date, YearsFuture as number) =>
let
//Find the number of days between the end date and the start date
NumberOfDates = Duration.Days(DateTime.Date(DateTime.FixedLocalNow()) – StartDate)+365*YearsFuture,
//Generate a continuous list of dates from the start date to the end date
DateList = List.Dates(StartDate, NumberOfDates, #duration(1, 0, 0, 0)),
//Turn this list into a table
TableFromList = Table.FromList(DateList, Splitter.SplitByNothing(), {“Date”}
, null, ExtraValues.Error),
//Caste the single column in the table to type date
ChangedType = Table.TransformColumnTypes(TableFromList,{{“Date”, type date}}),
//Add custom columns for day of month, month number, year
DayOfMonth = Table.AddColumn(ChangedType, “DayOfMonth”, each Date.Day([Date]), Int64.Type),
MonthNumber = Table.AddColumn(DayOfMonth, “MonthNumberOfYear”, each Date.Month([Date]), Int64.Type),
Year = Table.AddColumn(MonthNumber, “Year”, each Date.Year([Date]), Int64.Type),
DayOfWeekNumber = Table.AddColumn(Year, “DayOfWeekNumber”, each Date.DayOfWeek([Date])+1, Int64.Type),
//Add month and day names
MonthName = Table.AddColumn(DayOfWeekNumber, “MonthName”, each Date.ToText([Date],”MMMM”,”cs”), type text),
DayName = Table.AddColumn(MonthName, “DayName”, each Date.ToText([Date],”dddd”, “cs”), type text)
in
DayName
in
CreateDateTable