topic Re: Dax Forecasting Calculation for stock over time in DAX Commands and Tips

Dax Forecasting Calculation for stock over time

Anonymous — Wed, 26 Feb 2020 14:06:01 GMT

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

Item	Initial Stock
Item A	1000
Item B	2000
Item C	3000

Dates	Item	Reposition	Consumption	Formula (Example)	Calculated Stock
11/01/2020	Item A	21	78	1000 + 21 - 78 = 943	943
12/01/2020	Item A	97	13	943 + 97 - 13 = 1027	1027
13/01/2020	Item A	99	5	1027 + 99 - 5 = 1121	1121
14/01/2020	Item A	76	69	1121 + 76 - 69 = 1128	1128
15/01/2020	Item A	88	97	1128 + 88 - 97 = 1119	1119
16/01/2020	Item A	95	35	1119 + 95 - 35 = 1179	1179
17/01/2020	Item A	79	35	1179 + 79 - 35 = 1223	1223
18/01/2020	Item A	1	23	1223 + 1 - 23 = 1201	1201
10/01/2020	Item B	67	12	2000 + 67 - 12 = 2055	1000
11/01/2020	Item B	83	78	1000 + 83 - 78 = 1005	1005
12/01/2020	Item B	98	95	1005 + 98 - 95 = 1008	1008
13/01/2020	Item B	75	10	1008 + 75 - 10 = 1073	1073
14/01/2020	Item B	60	36	1073 + 60 - 36 = 1097	1097
15/01/2020	Item B	96	37	1097 + 96 - 37 = 1156	1156
16/01/2020	Item B	22	59	1156 + 22 - 59 = 1119	1119
17/01/2020	Item B	82	24	1119 + 82 - 24 = 1177	1177
18/01/2020	Item B	87	76	1177 + 87 - 76 = 1188	1188
10/01/2020	Item C	4	42	3000 + 4 - 42 = 2962	1000
11/01/2020	Item C	61	66	1000 + 61 - 66 = 995	995
12/01/2020	Item C	57	64	995 + 57 - 64 = 988	988
13/01/2020	Item C	44	16	988 + 44 - 16 = 1016	1016
14/01/2020	Item C	22	40	1016 + 22 - 40 = 998	998
15/01/2020	Item C	25	67	998 + 25 - 67 = 956	956
16/01/2020	Item C	46	29	956 + 46 - 29 = 973	973
17/01/2020	Item C	16	3	973 + 16 - 3 = 986	986
18/01/2020	Item C	95	91	986 + 95 - 91 = 990	990

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.

Re: Dax Forecasting Calculation for stock over time

sturlaws — Mon, 24 Feb 2020 10:04:51 GMT

Hi Anonymous

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

Cheers,
Sturla

Re: Dax Forecasting Calculation for stock over time

Anonymous — Wed, 26 Feb 2020 11:14:04 GMT

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

Re: Dax Forecasting Calculation for stock over time

Anonymous — Wed, 26 Feb 2020 14:12:45 GMT

Take a look at my solution
hope it will help you

Re: Dax Forecasting Calculation for stock over time

Anonymous — Wed, 26 Feb 2020 12:34:33 GMT

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

Re: Dax Forecasting Calculation for stock over time

Anonymous — Wed, 26 Feb 2020 12:33:19 GMT

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

Re: Dax Forecasting Calculation for stock over time

sturlaws — Wed, 26 Feb 2020 13:42:15 GMT

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?

Re: Dax Forecasting Calculation for stock over time

Anonymous — Wed, 26 Feb 2020 14:06:55 GMT

The first row was an error, I removed it now.

I will try your solution.

Re: Dax Forecasting Calculation for stock over time

Anonymous — Thu, 27 Feb 2020 12:49:42 GMT

I tried your solution and although it works for one item at a time, It still takes too much time to process and crashes if more than one item is selected.
I want to try solving this problem in Power Query but I need some help because all the predicted consumptions, workdays, crossjoin tables, etc. were created in DAX and have to be remade in Power Query. Should I explain the problem here or should I create a new post dedicated to Power Query help?

Re: Dax Forecasting Calculation for stock over time

Anonymous — Thu, 27 Feb 2020 12:51:51 GMT

Yes
I will be better to create a new post to Power Query help