I will need your help with the problem I am facing regarding DAX:
We have a table that shows the when a new batch of cases will be generated and how many cases are expected:
idx batch_date new_cases
1 5/1/2020 1504
2 5/3/2020 2005
3 5/13/2020 921
4 5/26/2020 770
we also know that on average, every day 95 cases will be worked and closed, and the assumption is that newer batch will not be worked until the previous one is completed; we also have calculations showing when batch reaches aging of 10 and 15.
idx batch_date new_cases production days batch_start batch end day_10 day_15
1 5/1/2020 1504 95 16 5/1/2020 5/17/2020 5/11/2020 5/16/2020
2 5/3/2020 2005 95 22 5/18/2020 6/9/2020 5/13/2020 5/18/2020
3 5/13/2020 921 95 10 6/10/2020 6/20/2020 5/23/2020 5/28/2020
4 5/26/2020 770 95 9 6/21/2020 6/30/2020 6/5/2020 6/10/2020
in regular Excel I would use these formulas:
days: =ROUNDUP([@[new_cases]]/[@production]],0)
batch_start: =if(*batch_end previous row* >=[@[batch_date]], *batch_end previous row*+1,[@[batch_date]])
batch_end: =[@[batch_start]]+[@[days]]
day_10: = [@[batch_date]]+10
day_15: = [@[batch_date]]+15
first problem: when I try to write a DAX calculated column for batch_start,
batch_start:= IF(LOOKUPVALUE(
forecast[batch_end], forecast[idx], forecast[idx] -1) >= forecast[batch_date],
forecast[batch_end], forecast[idx], forecast[idx] -1) +1,
forecast[batch_date])
I obviously get a circular dependency error. Any idea how to resolve that?
The other problem is that the ultimate goal of this exercise is to count how many cases we have in each aging bucket (<=10, 11-15, >15) on every day. I.e., on 5/2 I have 1409 open cases from 5/1 batch (1504 - 95 already processed), all in <=10 bucket; on 5/28 I have 770 in <=10 bucket, 921 in 11-15 bucket and 1055 in >15 bucket etc. Any idea how to write a measure that would show that? I put on DropBox a xlsx file where I manually counted the expected outcome (https://www.dropbox.com/s/pgfgdtcxqilxfnm/forecast.xlsx?dl=0). The reason I need this in DAX is that previous steps of the entire project are done using PowerQuery, PowerPivot and DAX, and this will be the very last puzzle of the project.
batch: expected outcome:
5/1 5/3 5/13 5/26 all open cases <=10 11-15 >15
2020-05-01 1504 1504 1504 0 0
2020-05-02 1409 1409 1409 0 0
2020-05-03 1314 2005 3319 3319 0 0
2020-05-04 1219 2005 3224 3224 0 0
2020-05-05 1124 2005 3129 3129 0 0
2020-05-06 1029 2005 3034 3034 0 0
2020-05-07 934 2005 2939 2939 0 0
2020-05-08 839 2005 2844 2844 0 0
2020-05-09 744 2005 2749 2749 0 0
2020-05-10 649 2005 2654 2654 0 0
2020-05-11 554 2005 2559 2559 0 0
2020-05-12 459 2005 2464 2005 459 0
2020-05-13 364 2005 921 3290 2926 364 0
2020-05-14 269 2005 921 3195 921 2274 0
2020-05-15 174 2005 921 3100 921 2179 0
2020-05-16 79 2005 921 3005 921 2084 0
2020-05-17 0 2005 921 2926 921 2005 0
2020-05-18 2005 921 2926 921 2005 0
2020-05-19 1910 921 2831 921 0 1910
2020-05-20 1815 921 2736 921 0 1815
2020-05-21 1720 921 2641 921 0 1720
2020-05-22 1625 921 2546 921 0 1625
2020-05-23 1530 921 2451 921 0 1530
2020-05-24 1435 921 2356 0 921 1435
2020-05-25 1340 921 2261 0 921 1340
2020-05-26 1245 921 770 2936 770 921 1245
2020-05-27 1150 921 770 2841 770 921 1150
2020-05-28 1055 921 770 2746 770 921 1055
2020-05-29 960 921 770 2651 770 0 1881
2020-05-30 865 921 770 2556 770 0 1786
2020-05-31 770 921 770 2461 770 0 1691
2020-06-01 675 921 770 2366 770 0 1596
2020-06-02 580 921 770 2271 770 0 1501
2020-06-03 485 921 770 2176 770 0 1406
2020-06-04 390 921 770 2081 770 0 1311
2020-06-05 295 921 770 1986 770 0 1216
2020-06-06 200 921 770 1891 0 770 1121
2020-06-07 105 921 770 1796 0 770 1026
2020-06-08 10 921 770 1701 0 770 931
2020-06-09 0 921 770 1691 0 770 921
2020-06-10 921 770 1691 0 770 921
2020-06-11 826 770 1596 0 0 1596
2020-06-12 731 770 1501 0 0 1501
2020-06-13 636 770 1406 0 0 1406
2020-06-14 541 770 1311 0 0 1311
2020-06-15 446 770 1216 0 0 1216
2020-06-16 351 770 1121 0 0 1121
2020-06-17 256 770 1026 0 0 1026
2020-06-18 161 770 931 0 0 931
2020-06-19 66 770 836 0 0 836
2020-06-20 0 770 770 0 0 770
2020-06-21 770 770 0 0 770
2020-06-22 675 675 0 0 675
2020-06-23 580 580 0 0 580
2020-06-24 485 485 0 0 485
2020-06-25 390 390 0 0 390
2020-06-26 295 295 0 0 295
2020-06-27 200 200 0 0 200
2020-06-28 105 105 0 0 105
2020-06-29 10 10 0 0 10
2020-06-30 0 0 0 0 0
2020-07-01 0 0 0 0 0
Again, I would appreciate your help.
Best regards, Michal
Assuming you have no gaps in production, you can calculate batch_start by taking the first batch_generation_date and rolling forward the cumulative number production days.
batch_start =
VAR FirstBatchDate = MIN ( forecast[batch_generation_date] )
VAR CurrBatchDate = forecast[batch_generation_date]
VAR CumulativeDays =
SUMX (
FILTER ( forecast, forecast[batch_generation_date] < CurrBatchDate ),
forecast[days] + 1
)
RETURN
FirstBatchDate + CumulativeDays