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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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