Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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
Is it possible to do in Power Query? Can you show me how?
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?
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?
Yes
I will be better to create a new post to Power Query help
The first row was an error, I removed it now.
I will try your solution.
HI
Take a look at my solution
hope it will help you
Hi @Anonymous
could you provide some sample data(or share your report)? And an example of the desired output?
Cheers,
Sturla
Yes, I placed an example of a table on the post above.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
39 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
44 | |
32 | |
30 | |
18 | |
17 |