Cumulative Measure with Dynamic Time Variable

[monsoonnut]

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.


[TABLE="width: 686"]
<tbody>[TR]
[TD="width: 110"]productID[/TD]
[TD="width: 64, align: right"]80316[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64, align: right"]283907[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]Total[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD]WeekInRange[/TD]
[TD]LT[/TD]
[TD]Fcst[/TD]
[TD]CumFcst[/TD]
[TD]LT[/TD]
[TD]Fcst[/TD]
[TD]CumFcst[/TD]
[TD]LT[/TD]
[TD]LT Fcst[/TD]
[TD] CumFcst[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]934[/TD]
[TD="align: right"]934[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]210[/TD]
[TD="align: right"]210[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1144[/TD]
[TD="align: right"]1144[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1297[/TD]
[TD="align: right"]2231[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]212[/TD]
[TD="align: right"]422[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1509[/TD]
[TD="align: right"]2653[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1370[/TD]
[TD="align: right"]3601[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]211[/TD]
[TD="align: right"]423[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1581[/TD]
[TD="align: right"]3090[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1542[/TD]
[TD="align: right"]5143[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]294[/TD]
[TD="align: right"]505[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1836[/TD]
[TD="align: right"]3417[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1675[/TD]
[TD="align: right"]5884[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]338[/TD]
[TD="align: right"]632[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2013[/TD]
[TD="align: right"]3849[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1631[/TD]
[TD="align: right"]6218[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]345[/TD]
[TD="align: right"]683[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1976[/TD]
[TD="align: right"]3989[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1651[/TD]
[TD="align: right"]6499[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]383[/TD]
[TD="align: right"]728[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2034[/TD]
[TD="align: right"]4010[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1672[/TD]
[TD="align: right"]6629[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]404[/TD]
[TD="align: right"]787[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2076[/TD]
[TD="align: right"]4110[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1658[/TD]
[TD="align: right"]6612[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]451[/TD]
[TD="align: right"]855[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2109[/TD]
[TD="align: right"]4185[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1643[/TD]
[TD="align: right"]6624[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]486[/TD]
[TD="align: right"]937[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2129[/TD]
[TD="align: right"]4238[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1630[/TD]
[TD="align: right"]6603[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]481[/TD]
[TD="align: right"]967[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2111[/TD]
[TD="align: right"]4240[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1573[/TD]
[TD="align: right"]6504[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]502[/TD]
[TD="align: right"]983[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2075[/TD]
[TD="align: right"]4186[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1621[/TD]
[TD="align: right"]6467[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]564[/TD]
[TD="align: right"]1066[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2185[/TD]
[TD="align: right"]4260[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1652[/TD]
[TD="align: right"]6476[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]641[/TD]
[TD="align: right"]1205[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2293[/TD]
[TD="align: right"]4478[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1644[/TD]
[TD="align: right"]6490[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]809[/TD]
[TD="align: right"]1450[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2453[/TD]
[TD="align: right"]4746[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1656[/TD]
[TD="align: right"]6573[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]890[/TD]
[TD="align: right"]1699[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2546[/TD]
[TD="align: right"]4999[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1679[/TD]
[TD="align: right"]6631[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1008[/TD]
[TD="align: right"]1898[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2687[/TD]
[TD="align: right"]5233[/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1696[/TD]
[TD="align: right"]6675[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1144[/TD]
[TD="align: right"]2152[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2840[/TD]
[TD="align: right"]5527[/TD]
[/TR]
[TR]
[TD="align: right"]19[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1710[/TD]
[TD="align: right"]6741[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1229[/TD]
[TD="align: right"]2373[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2939[/TD]
[TD="align: right"]5779[/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]21[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]22[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]23[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]24[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]25[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]