Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Dax Forecasting Calculation for stock over time

Hello everyone,

 

The logic to apply over my problem is very simple but I can't seem to find a DAX formula that runs this without running out of memory or producing errors

 

I have a table created by Generate(Table with Every Product, Calendar with 365 days, from 6 months in the past to 6 months to the future), so I have a table with everyday for every product. I have 3 additional columns:
- Calculated consumption every day (both registered in the past and calculated in the future);
- Restocking every day (only necessary in the future 6 months because the stock will be calculated only in the future)
- Initial Stock today

I want to calculate the stock in the future:
- First Calculation (Tomorrow): Stock(Tomorrow) = Initial Stock - Consumption(Tomorrow) + Restocking(Tomorrow)
- Every other future day: Stock(Day N) = Stock(Day N-1) - Consumption(Day N) + Restocking(Day N)

 

In Excel this is very easy to do, add consumption and restocking to the stock on the previous cell (See tables below).

ItemInitial Stock
Item A1000
Item B2000
Item C3000

 

DatesItemRepositionConsumptionFormula (Example)Calculated Stock
11/01/2020Item A21781000 + 21 - 78 = 943943
12/01/2020Item A9713943 + 97 - 13 = 10271027
13/01/2020Item A9951027 + 99 - 5 = 11211121
14/01/2020Item A76691121 + 76 - 69 = 11281128
15/01/2020Item A88971128 + 88 - 97 = 11191119
16/01/2020Item A95351119 + 95 - 35 = 11791179
17/01/2020Item A79351179 + 79 - 35 = 12231223
18/01/2020Item A1231223 + 1 - 23 = 12011201
10/01/2020Item B67122000 + 67 - 12 = 20551000
11/01/2020Item B83781000 + 83 - 78 = 10051005
12/01/2020Item B98951005 + 98 - 95 = 10081008
13/01/2020Item B75101008 + 75 - 10 = 10731073
14/01/2020Item B60361073 + 60 - 36 = 10971097
15/01/2020Item B96371097 + 96 - 37 = 11561156
16/01/2020Item B22591156 + 22 - 59 = 11191119
17/01/2020Item B82241119 + 82 - 24 = 11771177
18/01/2020Item B87761177 + 87 - 76 = 11881188
10/01/2020Item C4423000 + 4 - 42 = 29621000
11/01/2020Item C61661000 + 61 - 66 = 995995
12/01/2020Item C5764995 + 57 - 64 = 988988
13/01/2020Item C4416988 + 44 - 16 = 10161016
14/01/2020Item C22401016 + 22 - 40 = 998998
15/01/2020Item C2567998 + 25 - 67 = 956956
16/01/2020Item C4629956 + 46 - 29 = 973973
17/01/2020Item C163973 + 16 - 3 = 986986
18/01/2020Item C9591986 + 95 - 91 = 990990

 

I want to reproduce this table in Power BI (with 10000 products and 180 days each).

In Power BI I have no idea how to use previous stock (Day N-1).
I tried using Data<=Max(Data), for just one product on 1 month it worked but, for around 10000 products multiplied by 180 days ( around 3,650,000), Power Bi ran out of memory. All the other calculations for this amount of rows were possible (Calculated consumption, etc.).
I used other methods that required measures and the formula Calculate, but Calculate removes exterior contexts and the stock was always the sum of the stock for every product and not individually.

 

Is there a more efficient way to calculate this?

I have a tool in excel with VBA doing this already but it takes to much time and its no flexible to change and illustrate the data. Power Bi is a faster and easier solution to manipulate.

DAX
DAX Calculation
Dax Help
Forecasting
Need Help

9 Replies

  • sturlaws's avatar
    sturlaws
    Icon for Resident Rockstar rankResident Rockstar
    6 years ago

    Hi Anonymous 

     

    could you provide some sample data(or share your report)? And an example of the desired output?

     

    Cheers,
    Sturla

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Yes, I placed an example of a table on the post above.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    HI

    Take a look at my solution 
    hope it will help you

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    You do such things in Power Query, not in DAX. Typical mistake. This is telescoping recursion that can be done in DAX but this is not the tool for such things. Please use the right tool for the right job. On top of that calculated columns are not well compressed.

    PowerQuery is the tool for such jobs.

    Best
    D

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Is it possible to do in Power Query? Can you show me how?

      • sturlaws's avatar
        sturlaws
        Icon for Resident Rockstar rankResident Rockstar
        6 years ago

        if you want to have the forecast as a column, you should use power query.

         

        If it is acceptable to use a measure, you can try this approach:

        Measure =
VAR _initial =
    CALCULATE (
        SUM ( 'initial value'[Initial Stock] );
        FILTER (
            'initial value';
            'initial value'[Item] = CALCULATE ( SELECTEDVALUE ( Data[Item] ) )
        )
    )
VAR _currentDate =
    CALCULATE ( SELECTEDVALUE ( Data[Dates] ) )
VAR _prevRep =
    CALCULATE (
        SUM ( Data[Reposition] );
        FILTER ( ALLEXCEPT ( Data; Data[Item] ); Data[Dates] <= _currentDate )
    )
VAR _prevCon =
    CALCULATE (
        SUM ( Data[Consumption] );
        FILTER ( ALLEXCEPT ( Data; Data[Item] ); Data[Dates] <= _currentDate )
    )
RETURN
    _initial + _prevRep - _prevCon

         

        I am a bit confused over the two first lines in your sample data, should calculated stock be 1000 on both of those two lines?