Forum Discussion

MiloPowerBI's avatar
MiloPowerBI
Frequent Visitor
3 years ago
Solved

Create Forward Looking Lags based on Fiscal Months

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.

 

Need Help
Tips and Tricks
  • olgad's avatar
    olgad
    3 years ago

    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"

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

2 Replies

  • olgad's avatar
    olgad
    Icon for Resident Rockstar rankResident Rockstar
    3 years ago

    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"

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