Create Forward Looking Lags based on Fiscal Months

[MiloPowerBI]

Hello all, 

I'm trying to work with a dataset that has a lot of forecasted/planned info. I want my report to have a page that shows Forecasted Sales and the user being able to see future forecasted sales by selecting a lag of their desire via a Slicer dropdown. I would like the current month to be Lag 0, next month to be Lag 1, the month after to be Lag 6 etc. 

I have a table in my dataset that is a Fiscal Calendar.

Can someone advise me on some DAX (or whatever works) to see the Current Fiscal Month as Lag 0 (zero) and then the next Fiscal Month as Lag 1, Lag 2 etc.

[MiloPowerBI]

Thank you olgad, that worked perfectly. 

[olgad · Accepted solution]

Hi, create a calculated column today=today()
then lags for the 12 months

lags =

if(EOMONTH('Date'[Date],0)=EOMONTH('Date'[today],0), "Lag 0",

if(EOMONTH('Date'[Date],0)=EOMONTH('Date'[today],1), "Lag 1",

if(EOMONTH('Date'[Date],0)=EOMONTH('Date'[today],2), "Lag 2",

if(EOMONTH('Date'[Date],0)=EOMONTH('Date'[today],3), "Lag 3",

if(EOMONTH('Date'[Date],0)=EOMONTH('Date'[today],4), "Lag 4",

if(EOMONTH('Date'[Date],0)=EOMONTH('Date'[today],5), "Lag 5",

if(EOMONTH('Date'[Date],0)=EOMONTH('Date'[today],6), "Lag 6",

if(EOMONTH('Date'[Date],0)=EOMONTH('Date'[today],7), "Lag 7",

if(EOMONTH('Date'[Date],0)=EOMONTH('Date'[today],8), "Lag 8",

if(EOMONTH('Date'[Date],0)=EOMONTH('Date'[today],9), "Lag 9",

if(EOMONTH('Date'[Date],0)=EOMONTH('Date'[today],10), "Lag 10",

if(EOMONTH('Date'[Date],0)=EOMONTH('Date'[today],11), "Lag 11"

     ))))))))))))