Getting the best out of your data in PowerBI often requires the creation of specialized calendars that help you analyze your data the that you need it. I’ve been creating calendars using Power Query. Another problem I’ve had is where to store simple reference data, like the aging buckets for an Aging Calendar. So I combined these two issues and this is my solution. I must emphasize that this is not the only one way of solving this problem and may not suit your requirements, but take my thoughts and see if you find them useful.
Here is the Power Query:
let
AgingTable = #table(
{"order","bucket","start","end"},
{
{1,"0-29",0,29},
{2,"30-59",30,59},
{3,"60-89",60,89},
{4,"90+",90,3560}
}
),
EndDate = Table.AddColumn(AgingTable, "EndDate", each Date.AddDays(DateTime.Date(DateTime.LocalNow()),-[start])),
StartDate = Table.AddColumn(EndDate, "StartDate", each Date.AddDays(DateTime.Date(DateTime.LocalNow()), -[end])),
Duration = Table.AddColumn(StartDate, "Duration", each Duration.Days([EndDate]-[StartDate])+1),
Datelist = Table.AddColumn(Duration, "DateList", each List.Dates([StartDate],[Duration],#duration(1,0,0,0))),
#"Expanded DateList" = Table.ExpandListColumn(Datelist, "DateList"),
#"Removed Columns" = Table.RemoveColumns(#"Expanded DateList",{"start", "end", "EndDate", "StartDate", "Duration"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"DateList", "Date"}})
in
#"Renamed Columns"
This is fairly simple, but put together creates, I think, an elegant solution for an aging calendar. There’s an order column in the AgingTable, so that you can get your buckets in the right sequence on reports. I’ve started the date from today, but you can use other options, such a parameters. You can also insert this query into a PowerBI service DataFlow, which I’m starting to favor for my PowerBI calendars. Another nice technique here is the use of the list to generate the dates. Lists are one of the most powerful features of Power Query.
Let me know what you think, all feedback is greatly appreciated. Oh, and this advice is offered without any guarantee 