cancel
Showing results 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.

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).

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
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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
2 REPLIES 2
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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

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

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

Announcements

#### 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

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors