monsoonnut
New Member
- Joined
- Jul 1, 2016
- Messages
- 13
Hi
I am yet again stumped by a DAX measure.
I am trying to create a measure of forecast over leadtime i.e. The cumulative forecast over the leadtime of the Product (which varies by product).
The calculation works at the Product level but fails during aggregation.
I cannot resolve how I use SUMX on a data table over a timeframe and include a third dynamic variable (Leadtime, (LT)).
LTWks is available on both the Products and data table.
The measure I am using is
CumFcst =
CALCULATE (
SUMX (data, Data[forecast] ),
DATESINPERIOD (
DimDate[datecymd],
LASTDATE ( DimDate[datecymd] ),
- ( FIRSTNONBLANK ( Data[LTwks], Data[LTwks] ) * 7 ),
DAY
),
products
)
As you can see at SKU level the Cum forecast is calculating correctly i.e. for week 19 ProductID 80316 with LTWks = 4 The CumFcst is 1710+1696+1679+1656=6741, a correct calculation and for ProductID 283907 with LTWks of 2 the CumFcst is 1229+1144=2973, again correct. However during the aggregation it is using LtWks =2. I understand that this is due to the use of FirstNonBlank but cannot find an alternative. I started using Values in its place but that causes an error. I also tried to calculate LT as a measure but couldn't get that to work either. Any help would be appreciated.
<tbody>
</tbody>
I am yet again stumped by a DAX measure.
I am trying to create a measure of forecast over leadtime i.e. The cumulative forecast over the leadtime of the Product (which varies by product).
The calculation works at the Product level but fails during aggregation.
I cannot resolve how I use SUMX on a data table over a timeframe and include a third dynamic variable (Leadtime, (LT)).
LTWks is available on both the Products and data table.
The measure I am using is
CumFcst =
CALCULATE (
SUMX (data, Data[forecast] ),
DATESINPERIOD (
DimDate[datecymd],
LASTDATE ( DimDate[datecymd] ),
- ( FIRSTNONBLANK ( Data[LTwks], Data[LTwks] ) * 7 ),
DAY
),
products
)
As you can see at SKU level the Cum forecast is calculating correctly i.e. for week 19 ProductID 80316 with LTWks = 4 The CumFcst is 1710+1696+1679+1656=6741, a correct calculation and for ProductID 283907 with LTWks of 2 the CumFcst is 1229+1144=2973, again correct. However during the aggregation it is using LtWks =2. I understand that this is due to the use of FirstNonBlank but cannot find an alternative. I started using Values in its place but that causes an error. I also tried to calculate LT as a measure but couldn't get that to work either. Any help would be appreciated.
productID | 80316 | 283907 | Total | ||||||
WeekInRange | LT | Fcst | CumFcst | LT | Fcst | CumFcst | LT | LT Fcst | CumFcst |
1 | 4 | 934 | 934 | 2 | 210 | 210 | 2 | 1144 | 1144 |
2 | 4 | 1297 | 2231 | 2 | 212 | 422 | 2 | 1509 | 2653 |
3 | 4 | 1370 | 3601 | 2 | 211 | 423 | 2 | 1581 | 3090 |
4 | 4 | 1542 | 5143 | 2 | 294 | 505 | 2 | 1836 | 3417 |
5 | 4 | 1675 | 5884 | 2 | 338 | 632 | 2 | 2013 | 3849 |
6 | 4 | 1631 | 6218 | 2 | 345 | 683 | 2 | 1976 | 3989 |
7 | 4 | 1651 | 6499 | 2 | 383 | 728 | 2 | 2034 | 4010 |
8 | 4 | 1672 | 6629 | 2 | 404 | 787 | 2 | 2076 | 4110 |
9 | 4 | 1658 | 6612 | 2 | 451 | 855 | 2 | 2109 | 4185 |
10 | 4 | 1643 | 6624 | 2 | 486 | 937 | 2 | 2129 | 4238 |
11 | 4 | 1630 | 6603 | 2 | 481 | 967 | 2 | 2111 | 4240 |
12 | 4 | 1573 | 6504 | 2 | 502 | 983 | 2 | 2075 | 4186 |
13 | 4 | 1621 | 6467 | 2 | 564 | 1066 | 2 | 2185 | 4260 |
14 | 4 | 1652 | 6476 | 2 | 641 | 1205 | 2 | 2293 | 4478 |
15 | 4 | 1644 | 6490 | 2 | 809 | 1450 | 2 | 2453 | 4746 |
16 | 4 | 1656 | 6573 | 2 | 890 | 1699 | 2 | 2546 | 4999 |
17 | 4 | 1679 | 6631 | 2 | 1008 | 1898 | 2 | 2687 | 5233 |
18 | 4 | 1696 | 6675 | 2 | 1144 | 2152 | 2 | 2840 | 5527 |
19 | 4 | 1710 | 6741 | 2 | 1229 | 2373 | 2 | 2939 | 5779 |
20 | 4 | 2 | 2 | ||||||
21 | 4 | 2 | 2 | ||||||
22 | 4 | 2 | 2 | ||||||
23 | 4 | 2 | 2 | ||||||
24 | 4 | 2 | 2 | ||||||
25 | 4 | 2 | 2 |
<tbody>
</tbody>