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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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!:
The Definitive Guide to Power Query (M)

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!:
The Definitive Guide to Power Query (M)

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors