Forum Discussion

Vahks's avatar
Vahks
Icon for Helper I rankHelper I
6 years ago

Get month measure result previous

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.

 

 

Need Help

6 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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/

     

     

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      Vahks
      Icon for Helper I rankHelper I
      6 years ago

      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's avatar
        Vahks
        Icon for Helper I rankHelper I
        6 years ago

        Hello,

         

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

         

        Thanks