Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
MiloPowerBI
Frequent Visitor

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.

 

1 ACCEPTED SOLUTION
olgad
Super User
Super User

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"

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

DID I ANSWER YOUR QUESTION? PLEASE MARK MY POST AS A SOLUTION! APPRECIATE YOUR KUDO/LIKE!
PROUD TO BE A SUPER USER!
Best Stories, Interesting Cases: PowerBI Storytime Newsletter
Linkedin Profile: Linkedin
YouTube Channel: PowerBI Storytime

View solution in original post

2 REPLIES 2
MiloPowerBI
Frequent Visitor

Thank you olgad, that worked perfectly. 

olgad
Super User
Super User

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"

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

DID I ANSWER YOUR QUESTION? PLEASE MARK MY POST AS A SOLUTION! APPRECIATE YOUR KUDO/LIKE!
PROUD TO BE A SUPER USER!
Best Stories, Interesting Cases: PowerBI Storytime Newsletter
Linkedin Profile: Linkedin
YouTube Channel: PowerBI Storytime

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors