Create a dynamic row for years

[Sergi]

Hi,

hope some one suport, I would like to have a dynamic row based on the years, which should change automatically over the years.

Example:

Row    Forecast    Year

1         Actual        2021

2         LY              2020

3         LY-1          2019

4         LY-2          2018

for next year should be:

Row    Forecast    Year

1         Actual        2022

2         LY              2021

3         LY-1          2020

4         LY-2          2019

thanks,

Sergi

[v-eqin-msft · Accepted solution]

Hi @Sergi ,

According to your description, it seems you want to display last 3 years based on current date.

If so, please enter a table firstly and then create a measure:

Measure = 
var _yearToday=YEAR(TODAY())
return
SWITCH(MAX('NewTable'[Forecast]),"Actual",_yearToday,"LY",_yearToday-1, "LY-1",_yearToday-2, "LY-2",_yearToday-3)

Output:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[VahidDM]

Hi @Sergi 

Can you share a sample of your data table?
BTW, Try this:

Row = 
Var _Y = Year(Today())
Var _MY = MAX(table[year])
return
_Y-_MY+1

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!!