Dynamic header for Date

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"