Dynamic header for Date

[adiljamal]

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 

[lbendlin · Accepted solution]

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

See attached.

[adiljamal]

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"

[lbendlin]

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.

[adiljamal]

Thanks @lbendlin .

That's super impressive and really helpful.

Very impressed with the logic.