DAX using result from past month

[imlaug]

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

[Greg_Deckler · Accepted solution]

@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

[lbendlin]

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.