Get month measure result previous

[Vahks]

Hello,

i would like to get the result of my measure for each previous month to calculate the value of the next month (column in red below) :

Here is my Dax Code for the column "Stock" :

Stock = var StockPastMonth =
CALCULATE(
[Qte stock] + [Finished PO] - [Invoiced];
FILTER(
Calendar;
Calendar[Num Month] < MONTH(NOW()) && Calendar[Year] = YEAR(NOW())
)
) + 0

var StockCurrentMonth =
CALCULATE(
CALCULATE(
[Qte stock] + [Finished PO] - [Invoiced];
DATEADD(Calendar[Date];-1;MONTH)
) + [Production Order] + [Supply Forecast];
FILTER(
Calendar;
Calendar[Num Month] = MONTH(NOW()) && Calendar[Year] = YEAR(NOW())
)
) + 0

var StockFutureMonth = CALCULATE(CALCULATE(CALCULATE(
[Qte stock] + [Finished PO] - [Invoiced];
DATEADD(Calendar[Date];-1;MONTH)
) + [Production Order] + [Supply Forecast] - [Max Expected Sales];
DATEADD(Calendar[Date];-1;MONTH)
) + [Production Order] + [Supply Forecast];
FILTER(
Calendar;
Calendar[Num Month] > MONTH(NOW()) && Calendar[Year] = YEAR(NOW())
)
) + 0

return IF( StockPastMonth <> 0; StockPastMonth; IF(StockCurrentMonth <> 0; StockCurrentMonth - [Max Expected Sales];IF(StockFutureMonth <> 0; StockFutureMonth - [Max Expected Sales] ;0)))

The column "Test1" is :

Test1 = CALCULATE(
[Qte stock] + [Finished PO] - [Invoiced] + [Production Order] + [Supply Forecast] - [Max Expected Sales];
DATEADD(Calendar[Date];-1;MONTH)
) 

And doesn't work.

My second test "Test2" is :

Test2 = CALCULATE(CALCULATE(
[Qte stock] + [Finished PO] - [Invoiced];
DATEADD(Calendar[Date];-1;MONTH)
) + [Production Order] + [Supply Forecast] - [Max Expected Sales];
DATEADD(Calendar[Date];-1;MONTH)
) 

this measure work for March and not for the next month, i don't understand the difference with the calcul of "Test1".

if someones can explain me, it will be a pleasure ?

How i can get the Stock of March to calculate Stock of April, etc.. etc..

Thanks for your help.

[MFelix]

Hi @Vahks ,

On the stock calculation what is the value you want to get?

Let's assume you only have 2 months data what would be the calculation made for the second month?

Looking at February for example is the end stock:

Qte Stock  + Finish PO  - Invoiced  + Production Order + Supply Forecast - Max Expected Sales

104           + (192 + 96) - (142+59) +          (0+96)                 + (0+0)             -  (110 +137)               = 40

Is this calculation correct?

Can you share a sample file please?

[Vahks]

Hello @MFelix ,

On the stock calculation what is the value you want to get?

My calculation is just different for the passed and current month, and this is correct. The problem it's just for the future after March.

I want to get the previous stock result for the future month.

Example, on my screenshot, you can see for March, the stock is 291. To calculate the stock for April, i need to get this 291, the calculation is :

Stock March + [Production Order] (April) + [Supply Forecast] (April) - [MaxExpectedSales] (April) 

291 + 0 + 0 - 110 = 181

For May, i need to get 181, so :

Stock April + [Production Order] (May) + [Supply Forecast] (May) - [MaxExpectedSales] (May) 

181 + 0 + 240 - 110 = 311

Etc etc.. for the next future month

Looking at February for example is the end stock:

Qte Stock + Finish PO - Invoiced + Production Order + Supply Forecast - Max Expected Sales

104 + (192 + 96) - (142+59) + (0+96) + (0+0) - (110 +137) = 40

No the calculation is by month, juste the stock result of previous month is needed, see my calculation above.

I can't share sample, but if it's needed i can try to make one.

Thanks

[Vahks]

Hello,

nobody can't help me ? my problem is not clear maybe ?

Thanks

[MFelix]

Hi @Vahks ,

I had some time constraints, and also have to be honest with some other notifications your went down in the list and I didn't return to it.

Can you please just clarify the way the model is setup:

Qte Stock - A single value in a table with 104? (without any date)

Finish PO / Invoiced / Production Order/ Suppply forecast one line per month?

How is you calendar table related to the other tables?

can you please also tell me what is the calculation for january? In your example you pick up the at March but the first month how do you calculate?

Are you abble to send out a mockup of your data?

[Vahks]

Hi @MFelix ,

No problem, i appreciate you taking time to help me.

---

Can you please just clarify the way the model is setup:

Qte Stock - A single value in a table with 104? (without any date)

Finish PO / Invoiced / Production Order/ Suppply forecast one line per month?

How is you calendar table related to the other tables?

Are you abble to send out a mockup of your data?

---

Yes Qte Stock have just a single value with no date, all others are grouping by month.

I send you in private message of link with an exemple of my data.

Thanks for your help

[amitchandak]

If you use time intelligence function, you can get easily using datesmtd and totalmtd. But that need date table

Example

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
last year MTD Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/