Dynamic header for Date

adiljamal — Wed, 14 Jun 2023 19:56:49 GMT

Hey Community,

I am trying to create a dynamic column header for my dashboard. My current table looks like this when the slicer filter is for 'Nov 2019'.

What I want is something like this:

Changes that I want to include:
Sum of Generation column reflects the Month & Year

M-1_Gen is the generation value of the previous month - So it should be named as prev month & year, i.e., Oct-2019

M-12_Gen is generation same month last year. So it should be Nov 2018.

And likewise.

I've included my sample files.

Any help will be very appreciated.

Thanks in advance.

Files Here

Re: Dynamic header for Date

lbendlin — Fri, 16 Jun 2023 00:15:21 GMT

This requires an extremely heavy lift and some major refactoring. Not sure if you want to go that route.

See attached.

Re: Dynamic header for Date

adiljamal — Wed, 21 Jun 2023 18:22:51 GMT

Thanks lbendlin .

That's super impressive and really helpful.

Very impressed with the logic.

Re: Dynamic header for Date

adiljamal — Tue, 09 Jan 2024 21:10:18 GMT

Hey lbendlin ,

Your solution has been really helpful in creating the dynamic dashboards I wanted. I was able to take it a step ahead and create additional dynamic columns, like the one included here.

I am trying to create multiple years as dynamic column. (Current year, Y-1,Y-2,Y+1,Y+2,Y+3) so 2021 to 2026 assuming current year is 2023 (data issue). And the table should look something like this:

As you can see the column names here are manually created since when I try using the calculations, I get all the years in my column even when I just ask for one year.

This is what I'm getting:

And the calculation formulae inserted is:

Gen/Cap Forecast = switch(SELECTEDVALUE('Actual Date Repository'[Attribute]),"YTD",[YTD],"Y",[Y Gen/Cap])

My Query Data is pasted below. I'm unable to figure the issue. Please help me fix my error.

let Source = Table.Distinct(Table.SelectColumns(DatesTbl,{"Year","MonthOfYear"})), #"Added Custom" = Table.AddColumn(Source, "Date", each #date([Year],[MonthOfYear],1),type date), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Month Year", each Date.ToText(#date([Year],[MonthOfYear],1),[Format = "MMM-yyyy"])), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "M-1", each Date.ToText(Date.AddMonths(#date([Year],[MonthOfYear],1),-1),[Format = "MMM-yyyy"])), #"Added Custom4" = Table.AddColumn(#"Added Custom2", "M-2", each Date.ToText(Date.AddMonths(#date([Year],[MonthOfYear],1),-2),[Format = "MMM-yyyy"])), #"Added Custom5" = Table.AddColumn(#"Added Custom4", "M-3", each Date.ToText(Date.AddMonths(#date([Year],[MonthOfYear],1),-3),[Format = "MMM-yyyy"])), #"Added Custom3" = Table.AddColumn(#"Added Custom5", "M-12", each Date.ToText(Date.AddMonths(#date([Year],[MonthOfYear],1),-12),[Format = "MMM-yyyy"])), #"Added Custom6" = Table.AddColumn(#"Added Custom3", "YTD", each "YTD-" & Date.ToText(#date([Year],[MonthOfYear],1),[Format = "yyyy"])), #"Added Custom7" = Table.AddColumn(#"Added Custom6", "YTD-1", each "YTD-" & Date.ToText(Date.AddMonths(#date([Year],[MonthOfYear],1),-12),[Format = "yyyy"])), #"Added Custom8" = Table.AddColumn(#"Added Custom7", "YTD-2", each "YTD-" & Date.ToText(Date.AddMonths(#date([Year],[MonthOfYear],1),-24),[Format = "yyyy"])), #"Added Custom11" = Table.AddColumn(#"Added Custom8", "Y", each Date.ToText(#date([Year],[MonthOfYear],1),[Format = "yyyy"])), #"Added Custom12" = Table.AddColumn(#"Added Custom11", "Y-1", each Date.ToText(Date.AddMonths(#date([Year],[MonthOfYear],1),-12),[Format = "yyyy"])), #"Added Custom13" = Table.AddColumn(#"Added Custom12", "Y-2", each Date.ToText(Date.AddMonths(#date([Year],[MonthOfYear],1),-24),[Format = "yyyy"])), #"Added Custom14" = Table.AddColumn(#"Added Custom13", "Y-3", each Date.ToText(Date.AddMonths(#date([Year],[MonthOfYear],1),-36),[Format = "yyyy"])), #"Added Custom15" = Table.AddColumn(#"Added Custom14", "Y+1", each Date.ToText(Date.AddMonths(#date([Year],[MonthOfYear],1),12),[Format = "yyyy"])), #"Added Custom16" = Table.AddColumn(#"Added Custom15", "Y+2", each Date.ToText(Date.AddMonths(#date([Year],[MonthOfYear],1),24),[Format = "yyyy"])), #"Added Custom17" = Table.AddColumn(#"Added Custom16", "Y+3", each Date.ToText(Date.AddMonths(#date([Year],[MonthOfYear],1),36),[Format = "yyyy"])), #"Inserted Literal2" = Table.AddColumn(#"Added Custom17", "YTD%", each "YTD%", type text), #"Inserted Literal" = Table.AddColumn(#"Inserted Literal2", "MoM", each "MoM", type text), #"Added Custom10" = Table.AddColumn(#"Inserted Literal", "YTD Diff", each "YTD Diff"), #"Renamed Columns" = Table.RenameColumns(#"Added Custom10",{{"MoM", "Month Over Month"}}), #"Inserted Merged Column" = Table.AddColumn(#"Added Custom10", "MoM%", each "MoM%", type text), #"Inserted Literal1" = Table.AddColumn(#"Inserted Merged Column", "YoY", each "YoY", type text), #"Inserted Merged Column1" = Table.AddColumn(#"Inserted Literal1", "YoY%", each Text.Combine({[YoY], "%"}), type text), #"Removed Columns" = Table.RemoveColumns(#"Inserted Merged Column1",{"Year", "MonthOfYear"}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Date"}, "Attribute", "Value"), #"Added Custom9" = Table.AddColumn(#"Unpivoted Other Columns", "Index", each if [Attribute]= "Month Year" then Int64.From(#date(2025,1,1)-[Date]) else if [Attribute]= "M-1" then Int64.From(#date(2025,1,1)-(Date.AddMonths([Date],-1))) else if [Attribute]= "M-2" then Int64.From(#date(2025,1,1)-(Date.AddMonths([Date],-2))) else if [Attribute]= "M-3" then Int64.From(#date(2025,1,1)-(Date.AddMonths([Date],-3))) else if [Attribute]= "M-12" then Int64.From(#date(2025,1,1)-(Date.AddYears([Date],-1))) else if [Attribute]= "YTD" then 10010-Date.Year([Date]) else if [Attribute]= "YTD-1" then 10011-Date.Year([Date]) else if [Attribute]= "YTD-2" then 10012-Date.Year([Date]) else if [Attribute]= "MoM" then 3700 else if [Attribute]= "MoM%" then 3701 else if [Attribute]= "YoY" then 3702 else if [Attribute]= "YoY%" then 3703 else if [Attribute]= "YTD Diff" then 10013-Date.Year([Date]) else if [Attribute]= "YTD%" then 10014-Date.Year([Date]) else if [Attribute]= "Y" then 10015-Date.Year([Date]) else if [Attribute]= "Y+1" then 10016-Date.Year([Date]) else if [Attribute]= "Y+2" then 10017-Date.Year([Date]) else if [Attribute]= "Y+3" then 10018-Date.Year([Date]) else if [Attribute]= "Y-1" then 10014-Date.Year([Date]) else if [Attribute]= "Y-2" then 10013-Date.Year([Date]) else if [Attribute]= "Y-3" then 10012-Date.Year([Date]) else 10005-Date.Year([Date]) ,Int64.Type) in #"Added Custom9"

Re: Dynamic header for Date

lbendlin — Tue, 09 Jan 2024 21:45:47 GMT

I think the first step would be to push the index base out from 2025 to something like 2030.

But it's been a long time, and maybe this is an opportunity to refactor the approach with something simpler or with Field Parameters or Calculation Groups.

topic Re: Dynamic header for Date in DAX Commands and Tips

Dynamic header for Date

Re: Dynamic header for Date

Re: Dynamic header for Date

Re: Dynamic header for Date

Re: Dynamic header for Date