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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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