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
imlaug
Frequent Visitor

DAX using result from past month

Hi,

 

I am trying to calculate a figure based on the same calculation from the year before. So i take the result EOM and use that in order to calculate the next month (see picture). 

imlaug_0-1599049457387.png

 

I have found a way to do it, but the formula is quite heavy, meaning it will run slowly.

Is there a better way of constructing this?


Formula that works

EOP Stock Excl Sugg. =
//TM
CALCULATE((Fact_Stock[Stock EOM]+[Inbound WHS Future])
-(([BP Forecast]-Fact_Invoiced[Invoiced])
*(1+Excel_Line_Discount[Exp Line Discount])
*(1+[Expected PR%]));
Dim_Calendar[Month Split]="TM+0")

+
//Beregning af TM+1
//TM **bleep** EOP
CALCULATE((Fact_Stock[Stock EOM]+[Inbound WHS Future])
-(([BP Forecast]-Fact_Invoiced[Invoiced])
*(1+Excel_Line_Discount[Exp Line Discount])
*(1+[Expected PR%]));
PREVIOUSMONTH(Dim_Calendar[Date]);
Dim_Calendar[Month Split]="TM+0")
//TM+1 inbound
+
CALCULATE([Inbound WHS Future];Dim_Calendar[Month Split]="TM+1")
-

CALCULATE(
(([BP Forecast]-Fact_Invoiced[Invoiced])
*(1+Excel_Line_Discount[Exp Line Discount])
*(1+[Expected PR%]));
Dim_Calendar[Month Split]="TM+1")

//Beregning af TM+2
//TM+1 **bleep** EOP
+
CALCULATE((Fact_Stock[Stock EOM]+[Inbound WHS Future])
-(([BP Forecast]-Fact_Invoiced[Invoiced])
*(1+Excel_Line_Discount[Exp Line Discount])
*(1+[Expected PR%]));
PREVIOUSMONTH(PREVIOUSMONTH(Dim_Calendar[Date]));
Dim_Calendar[Month Split]="TM+0")

+
CALCULATE([Inbound WHS Future];Dim_Calendar[Month Split]="TM+1";
PREVIOUSMONTH(Dim_Calendar[Date]))
-

CALCULATE(
(([BP Forecast]-Fact_Invoiced[Invoiced])
*(1+Excel_Line_Discount[Exp Line Discount])
*(1+[Expected PR%]));
Dim_Calendar[Month Split]="TM+1";
PREVIOUSMONTH(Dim_Calendar[Date]))

//TM+2 inbound
+
CALCULATE([Inbound WHS Future];Dim_Calendar[Month Split]="TM+2")
-

CALCULATE(
(([BP Forecast]-Fact_Invoiced[Invoiced])
*(1+Excel_Line_Discount[Exp Line Discount])
*(1+[Expected PR%]));
Dim_Calendar[Month Split]="TM+2")

//Beregning af TM+3
//TM+2 **bleep** EOP
+
CALCULATE((Fact_Stock[Stock EOM]+[Inbound WHS Future])
-(([BP Forecast]-Fact_Invoiced[Invoiced])
*(1+Excel_Line_Discount[Exp Line Discount])
*(1+[Expected PR%]));
PREVIOUSMONTH(PREVIOUSMONTH(PREVIOUSMONTH(Dim_Calendar[Date])));
Dim_Calendar[Month Split]="TM+0")

+
CALCULATE([Inbound WHS Future];Dim_Calendar[Month Split]="TM+1";
PREVIOUSMONTH(PREVIOUSMONTH(Dim_Calendar[Date])))
-

CALCULATE(
(([BP Forecast]-Fact_Invoiced[Invoiced])
*(1+Excel_Line_Discount[Exp Line Discount])
*(1+[Expected PR%]));
Dim_Calendar[Month Split]="TM+1";
PREVIOUSMONTH(PREVIOUSMONTH(Dim_Calendar[Date])))

+
CALCULATE([Inbound WHS Future];Dim_Calendar[Month Split]="TM+2";
PREVIOUSMONTH(Dim_Calendar[Date]))
-

CALCULATE(
(([BP Forecast]-Fact_Invoiced[Invoiced])
*(1+Excel_Line_Discount[Exp Line Discount])
*(1+[Expected PR%]));
Dim_Calendar[Month Split]="TM+2";
PREVIOUSMONTH(Dim_Calendar[Date]))

//TM+3 inbound
+
CALCULATE([Inbound WHS Future];Dim_Calendar[Month Split]="TM+3")
-

CALCULATE(
(([BP Forecast]-Fact_Invoiced[Invoiced])
*(1+Excel_Line_Discount[Exp Line Discount])
*(1+[Expected PR%]));
Dim_Calendar[Month Split]="TM+3")

//Beregning af TM+4
//TM+3 **bleep** EOP
+
CALCULATE((Fact_Stock[Stock EOM]+[Inbound WHS Future])
-(([BP Forecast]-Fact_Invoiced[Invoiced])
*(1+Excel_Line_Discount[Exp Line Discount])
*(1+[Expected PR%]));
PREVIOUSMONTH(PREVIOUSMONTH(PREVIOUSMONTH(PREVIOUSMONTH(Dim_Calendar[Date]))));
Dim_Calendar[Month Split]="TM+0")

+
CALCULATE([Inbound WHS Future];Dim_Calendar[Month Split]="TM+1";
PREVIOUSMONTH(PREVIOUSMONTH(PREVIOUSMONTH(Dim_Calendar[Date]))))
-

CALCULATE(
(([BP Forecast]-Fact_Invoiced[Invoiced])
*(1+Excel_Line_Discount[Exp Line Discount])
*(1+[Expected PR%]));
Dim_Calendar[Month Split]="TM+1";
PREVIOUSMONTH(PREVIOUSMONTH(PREVIOUSMONTH(Dim_Calendar[Date]))))

+
CALCULATE([Inbound WHS Future];Dim_Calendar[Month Split]="TM+2";
PREVIOUSMONTH(PREVIOUSMONTH(Dim_Calendar[Date])))
-

CALCULATE(
(([BP Forecast]-Fact_Invoiced[Invoiced])
*(1+Excel_Line_Discount[Exp Line Discount])
*(1+[Expected PR%]));
Dim_Calendar[Month Split]="TM+2";
PREVIOUSMONTH(PREVIOUSMONTH(Dim_Calendar[Date])))

 
+
CALCULATE([Inbound WHS Future];Dim_Calendar[Month Split]="TM+3";
PREVIOUSMONTH(Dim_Calendar[Date]))
-

CALCULATE(
(([BP Forecast]-Fact_Invoiced[Invoiced])
*(1+Excel_Line_Discount[Exp Line Discount])
*(1+[Expected PR%]));
Dim_Calendar[Month Split]="TM+3";
PREVIOUSMONTH(Dim_Calendar[Date]))

//TM+4 inbound
+
CALCULATE([Inbound WHS Future];Dim_Calendar[Month Split]="TM+4")
-

CALCULATE(
(([BP Forecast]-Fact_Invoiced[Invoiced])
*(1+Excel_Line_Discount[Exp Line Discount])
*(1+[Expected PR%]));
Dim_Calendar[Month Split]="TM+4")

//Beregning af TM+5
//TM+4 **bleep** EOP
+
CALCULATE((Fact_Stock[Stock EOM]+[Inbound WHS Future])
-(([BP Forecast]-Fact_Invoiced[Invoiced])
*(1+Excel_Line_Discount[Exp Line Discount])
*(1+[Expected PR%]));
PREVIOUSMONTH(PREVIOUSMONTH(PREVIOUSMONTH(PREVIOUSMONTH(PREVIOUSMONTH(Dim_Calendar[Date])))));
Dim_Calendar[Month Split]="TM+0")

+
CALCULATE([Inbound WHS Future];Dim_Calendar[Month Split]="TM+1";
PREVIOUSMONTH(PREVIOUSMONTH(PREVIOUSMONTH(PREVIOUSMONTH(Dim_Calendar[Date])))))
-

CALCULATE(
(([BP Forecast]-Fact_Invoiced[Invoiced])
*(1+Excel_Line_Discount[Exp Line Discount])
*(1+[Expected PR%]));
Dim_Calendar[Month Split]="TM+1";
PREVIOUSMONTH(PREVIOUSMONTH(PREVIOUSMONTH(PREVIOUSMONTH(Dim_Calendar[Date])))))

+
CALCULATE([Inbound WHS Future];Dim_Calendar[Month Split]="TM+2";
PREVIOUSMONTH(PREVIOUSMONTH(PREVIOUSMONTH(Dim_Calendar[Date]))))
-

CALCULATE(
(([BP Forecast]-Fact_Invoiced[Invoiced])
*(1+Excel_Line_Discount[Exp Line Discount])
*(1+[Expected PR%]));
Dim_Calendar[Month Split]="TM+2";
PREVIOUSMONTH(PREVIOUSMONTH(PREVIOUSMONTH(Dim_Calendar[Date]))))

 
+
CALCULATE([Inbound WHS Future];Dim_Calendar[Month Split]="TM+3";
PREVIOUSMONTH(PREVIOUSMONTH(Dim_Calendar[Date])))
-

CALCULATE(
(([BP Forecast]-Fact_Invoiced[Invoiced])
*(1+Excel_Line_Discount[Exp Line Discount])
*(1+[Expected PR%]));
Dim_Calendar[Month Split]="TM+3";
PREVIOUSMONTH(PREVIOUSMONTH(Dim_Calendar[Date])))

+
CALCULATE([Inbound WHS Future];Dim_Calendar[Month Split]="TM+4";
PREVIOUSMONTH(Dim_Calendar[Date]))
-

CALCULATE(
(([BP Forecast]-Fact_Invoiced[Invoiced])
*(1+Excel_Line_Discount[Exp Line Discount])
*(1+[Expected PR%]));
Dim_Calendar[Month Split]="TM+4";
PREVIOUSMONTH(Dim_Calendar[Date]))

//TM+5 inbound
+
CALCULATE([Inbound WHS Future];Dim_Calendar[Month Split]="TM+5")
-

CALCULATE(
(([BP Forecast]-Fact_Invoiced[Invoiced])
*(1+Excel_Line_Discount[Exp Line Discount])
*(1+[Expected PR%]));
Dim_Calendar[Month Split]="TM+5")

//Beregning af TM+6
//TM+5 **bleep** EOP
+
CALCULATE((Fact_Stock[Stock EOM]+[Inbound WHS Future])
-(([BP Forecast]-Fact_Invoiced[Invoiced])
*(1+Excel_Line_Discount[Exp Line Discount])
*(1+[Expected PR%]));
PREVIOUSMONTH(PREVIOUSMONTH(PREVIOUSMONTH(PREVIOUSMONTH(PREVIOUSMONTH(PREVIOUSMONTH(Dim_Calendar[Date]))))));
Dim_Calendar[Month Split]="TM+0")

+
CALCULATE([Inbound WHS Future];Dim_Calendar[Month Split]="TM+1";
PREVIOUSMONTH(PREVIOUSMONTH(PREVIOUSMONTH(PREVIOUSMONTH(PREVIOUSMONTH(Dim_Calendar[Date]))))))
-

CALCULATE(
(([BP Forecast]-Fact_Invoiced[Invoiced])
*(1+Excel_Line_Discount[Exp Line Discount])
*(1+[Expected PR%]));
Dim_Calendar[Month Split]="TM+1";
PREVIOUSMONTH(PREVIOUSMONTH(PREVIOUSMONTH(PREVIOUSMONTH(PREVIOUSMONTH(Dim_Calendar[Date]))))))

+
CALCULATE([Inbound WHS Future];Dim_Calendar[Month Split]="TM+2";
PREVIOUSMONTH(PREVIOUSMONTH(PREVIOUSMONTH(PREVIOUSMONTH(Dim_Calendar[Date])))))
-

CALCULATE(
(([BP Forecast]-Fact_Invoiced[Invoiced])
*(1+Excel_Line_Discount[Exp Line Discount])
*(1+[Expected PR%]));
Dim_Calendar[Month Split]="TM+2";
PREVIOUSMONTH(PREVIOUSMONTH(PREVIOUSMONTH(PREVIOUSMONTH(Dim_Calendar[Date])))))

 
+
CALCULATE([Inbound WHS Future];Dim_Calendar[Month Split]="TM+3";
PREVIOUSMONTH(PREVIOUSMONTH(PREVIOUSMONTH(Dim_Calendar[Date]))))
-

CALCULATE(
(([BP Forecast]-Fact_Invoiced[Invoiced])
*(1+Excel_Line_Discount[Exp Line Discount])
*(1+[Expected PR%]));
Dim_Calendar[Month Split]="TM+3";
PREVIOUSMONTH(PREVIOUSMONTH(PREVIOUSMONTH(Dim_Calendar[Date]))))

+
CALCULATE([Inbound WHS Future];Dim_Calendar[Month Split]="TM+4";
PREVIOUSMONTH(PREVIOUSMONTH(Dim_Calendar[Date])))
-

CALCULATE(
(([BP Forecast]-Fact_Invoiced[Invoiced])
*(1+Excel_Line_Discount[Exp Line Discount])
*(1+[Expected PR%]));
Dim_Calendar[Month Split]="TM+4";
PREVIOUSMONTH(PREVIOUSMONTH(Dim_Calendar[Date])))


+
CALCULATE([Inbound WHS Future];Dim_Calendar[Month Split]="TM+5";
PREVIOUSMONTH(Dim_Calendar[Date]))
-

CALCULATE(
(([BP Forecast]-Fact_Invoiced[Invoiced])
*(1+Excel_Line_Discount[Exp Line Discount])
*(1+[Expected PR%]));
Dim_Calendar[Month Split]="TM+5";
PREVIOUSMONTH(Dim_Calendar[Date]))

//TM+6 inbound
+
CALCULATE([Inbound WHS Future];Dim_Calendar[Month Split]="TM+6")
-

CALCULATE(
(([BP Forecast]-Fact_Invoiced[Invoiced])
*(1+Excel_Line_Discount[Exp Line Discount])
*(1+[Expected PR%]));
Dim_Calendar[Month Split]="TM+6")

 

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@imlaug - I am with @lbendlin! Need source data in something that we can copy and paste to play with or better yet the PBIX file. However, what you have in your image kind of looks like Across then Down. I created a quick measure for it in the gallery but it is rubbish. However, I did include a far better version in my book DAX Cookbook. You can grab the DAX code here, it is recipe 6 in Chapter 12. https://github.com/gdeckler/DAXCookbook


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
Greg_Deckler
Super User
Super User

@imlaug - I am with @lbendlin! Need source data in something that we can copy and paste to play with or better yet the PBIX file. However, what you have in your image kind of looks like Across then Down. I created a quick measure for it in the gallery but it is rubbish. However, I did include a far better version in my book DAX Cookbook. You can grab the DAX code here, it is recipe 6 in Chapter 12. https://github.com/gdeckler/DAXCookbook


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
lbendlin
Super User
Super User

what medication are you taking for your headaches?  🙂

 

Describe the business rule for the total value of any given month,  and provide your sample data in usable format.

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.

Top Solution Authors