Cumulative Sum of qty based on multiple dates (forecast plan date and forecast date)

[Anonymous]

Hello,

Need some help or guidance to fix the problem on dax that is expected to return the Cumulative sum of qty

The cumulative measure works the report is filtered to one forecast plan date and one product.. 

Let me know if you need any detials.. 

Total Fcst_Qty = CALCULATE(Sum('Forecast-Cumulative'[Qty]),ALLSELECTED('Forecast-Cumulative'))

Cumulative Fcst_Qty = CALCULATE([Total Fcst_Qty],FILTER(ALLSELECTED('Forecast-Cumulative'),'Forecast-Cumulative'[fcstDate]<=MAX('Forecast-Cumulative'[fcstDate])))

The measure seems to be working when the report is filtered to one product and one Forecast plan date .

Out put of cumulative measure in correct as the value is same across all the dates and products

Dataset columns and its relationship

Below is the sample data.. due to restiction to post not more than 20k size i have added the sample data of one Forecast plan date..  

fcstplandate	Vendor	Product-Part	fcstDate	Qty
8/6/2021	Vendor-1	Product-Part-1	8/9/2021	207
8/6/2021	Vendor-2	Product-Part-2	8/9/2021	357
8/6/2021	Vendor-1	Product-Part-1	8/16/2021	288
8/6/2021	Vendor-2	Product-Part-2	8/16/2021	21
8/6/2021	Vendor-1	Product-Part-1	8/23/2021	286
8/6/2021	Vendor-2	Product-Part-2	8/23/2021	21
8/6/2021	Vendor-1	Product-Part-1	8/30/2021	282
8/6/2021	Vendor-2	Product-Part-2	8/30/2021	21
8/6/2021	Vendor-1	Product-Part-1	9/6/2021	280
8/6/2021	Vendor-2	Product-Part-2	9/6/2021	21
8/6/2021	Vendor-1	Product-Part-1	9/13/2021	276
8/6/2021	Vendor-2	Product-Part-2	9/13/2021	20
8/6/2021	Vendor-1	Product-Part-1	9/20/2021	272
8/6/2021	Vendor-2	Product-Part-2	9/20/2021	19
8/6/2021	Vendor-1	Product-Part-1	9/27/2021	270
8/6/2021	Vendor-2	Product-Part-2	9/27/2021	19
8/6/2021	Vendor-1	Product-Part-1	10/4/2021	266
8/6/2021	Vendor-2	Product-Part-2	10/4/2021	19
8/6/2021	Vendor-1	Product-Part-1	10/11/2021	262
8/6/2021	Vendor-2	Product-Part-2	10/11/2021	18
8/6/2021	Vendor-1	Product-Part-1	10/18/2021	260
8/6/2021	Vendor-2	Product-Part-2	10/18/2021	18
8/6/2021	Vendor-1	Product-Part-1	10/25/2021	256
8/6/2021	Vendor-2	Product-Part-2	10/25/2021	16
8/6/2021	Vendor-1	Product-Part-1	11/1/2021	253
8/6/2021	Vendor-2	Product-Part-2	11/1/2021	16
8/6/2021	Vendor-1	Product-Part-1	11/8/2021	250
8/6/2021	Vendor-2	Product-Part-2	11/8/2021	16
8/6/2021	Vendor-1	Product-Part-1	11/15/2021	247
8/6/2021	Vendor-2	Product-Part-2	11/15/2021	16
8/6/2021	Vendor-1	Product-Part-1	11/22/2021	243
8/6/2021	Vendor-2	Product-Part-2	11/22/2021	16
8/6/2021	Vendor-1	Product-Part-1	11/29/2021	240
8/6/2021	Vendor-2	Product-Part-2	11/29/2021	16
8/6/2021	Vendor-1	Product-Part-1	12/6/2021	237
8/6/2021	Vendor-2	Product-Part-2	12/6/2021	15
8/6/2021	Vendor-1	Product-Part-1	12/13/2021	234
8/6/2021	Vendor-2	Product-Part-2	12/13/2021	15
8/6/2021	Vendor-1	Product-Part-1	12/20/2021	231
8/6/2021	Vendor-2	Product-Part-2	12/20/2021	15
8/6/2021	Vendor-1	Product-Part-1	12/27/2021	228
8/6/2021	Vendor-2	Product-Part-2	12/27/2021	15
8/6/2021	Vendor-1	Product-Part-1	1/3/2022	225
8/6/2021	Vendor-2	Product-Part-2	1/3/2022	14
8/6/2021	Vendor-1	Product-Part-1	1/10/2022	222
8/6/2021	Vendor-2	Product-Part-2	1/10/2022	14
8/6/2021	Vendor-1	Product-Part-1	1/17/2022	220
8/6/2021	Vendor-2	Product-Part-2	1/17/2022	13
8/6/2021	Vendor-1	Product-Part-1	1/24/2022	217
8/6/2021	Vendor-2	Product-Part-2	1/24/2022	14
8/6/2021	Vendor-1	Product-Part-1	1/31/2022	215
8/6/2021	Vendor-2	Product-Part-2	1/31/2022	13
8/6/2021	Vendor-1	Product-Part-1	2/7/2022	212
8/6/2021	Vendor-2	Product-Part-2	2/7/2022	14
8/6/2021	Vendor-1	Product-Part-1	2/14/2022	210
8/6/2021	Vendor-2	Product-Part-2	2/14/2022	12
8/6/2021	Vendor-1	Product-Part-1	2/21/2022	207
8/6/2021	Vendor-2	Product-Part-2	2/21/2022	12
8/6/2021	Vendor-1	Product-Part-1	2/28/2022	204
8/6/2021	Vendor-2	Product-Part-2	2/28/2022	12
8/6/2021	Vendor-1	Product-Part-1	3/7/2022	201
8/6/2021	Vendor-2	Product-Part-2	3/7/2022	12
8/6/2021	Vendor-1	Product-Part-1	3/14/2022	199
8/6/2021	Vendor-2	Product-Part-2	3/14/2022	12
8/6/2021	Vendor-1	Product-Part-1	3/21/2022	196
8/6/2021	Vendor-2	Product-Part-2	3/21/2022	11
8/6/2021	Vendor-1	Product-Part-1	3/28/2022	192
8/6/2021	Vendor-2	Product-Part-2	3/28/2022	11
8/6/2021	Vendor-1	Product-Part-1	4/4/2022	191
8/6/2021	Vendor-2	Product-Part-2	4/4/2022	11
8/6/2021	Vendor-1	Product-Part-1	4/11/2022	187
8/6/2021	Vendor-2	Product-Part-2	4/11/2022	11
8/6/2021	Vendor-1	Product-Part-1	4/18/2022	185
8/6/2021	Vendor-2	Product-Part-2	4/18/2022	11
8/6/2021	Vendor-1	Product-Part-1	4/25/2022	180
8/6/2021	Vendor-2	Product-Part-2	4/25/2022	10
8/6/2021	Vendor-1	Product-Part-1	5/2/2022	179
8/6/2021	Vendor-2	Product-Part-2	5/2/2022	10
8/6/2021	Vendor-1	Product-Part-1	5/9/2022	176
8/6/2021	Vendor-2	Product-Part-2	5/9/2022	10
8/6/2021	Vendor-1	Product-Part-1	5/16/2022	173
8/6/2021	Vendor-2	Product-Part-2	5/16/2022	10
8/6/2021	Vendor-1	Product-Part-1	5/23/2022	169
8/6/2021	Vendor-2	Product-Part-2	5/23/2022	10
8/6/2021	Vendor-1	Product-Part-1	5/30/2022	166
8/6/2021	Vendor-2	Product-Part-2	5/30/2022	10
8/6/2021	Vendor-1	Product-Part-1	6/6/2022	163
8/6/2021	Vendor-2	Product-Part-2	6/6/2022	9
8/6/2021	Vendor-1	Product-Part-1	6/13/2022	161
8/6/2021	Vendor-2	Product-Part-2	6/13/2022	4
8/6/2021	Vendor-1	Product-Part-1	6/20/2022	157
8/6/2021	Vendor-2	Product-Part-2	6/20/2022	4
8/6/2021	Vendor-1	Product-Part-1	6/27/2022	155
8/6/2021	Vendor-2	Product-Part-2	6/27/2022	4
8/6/2021	Vendor-1	Product-Part-1	7/4/2022	151
8/6/2021	Vendor-2	Product-Part-2	7/4/2022	4
8/6/2021	Vendor-1	Product-Part-1	7/11/2022	147
8/6/2021	Vendor-2	Product-Part-2	7/11/2022	4
8/6/2021	Vendor-1	Product-Part-1	7/18/2022	145
8/6/2021	Vendor-2	Product-Part-2	7/18/2022	4
8/6/2021	Vendor-1	Product-Part-1	7/25/2022	141
8/6/2021	Vendor-2	Product-Part-2	7/25/2022	4

[Jos_Woolley]

Hi,

Can you clarify your use of ALLSELECTED here?

I would've thought you should be using simply:

Total Fcst_Qty =
CALCULATE ( SUM ( 'Forecast-Cumulative'[Qty] ), 'Forecast-Cumulative' )

Cumulative Fcst_Qty =
CALCULATE (
    [Total Fcst_Qty],
    FILTER (
        ALLEXCEPT ( 'Forecast-Cumulative', 'Forecast-Cumulative'[Product-Part] ),
        'Forecast-Cumulative'[fcstDate] <= MAX ( 'Forecast-Cumulative'[fcstDate] )
    )
)

Regards

[Anonymous]

Thank you @Jos_Woolley for your response.
Use of Allselected was to make sure that the cumulative is calulated for selected values from the slicers or matrix..

I made measure changes as per your suggestion but looks like the cumulative is not getting filtered for the fcstdate against the respective fcstplandate.
For example in the below snip the 08/09/2021 fcstdate & 08/07/2021 fcstplandate,  the qty is 357 but the cumulative is calculated as sum of all 08/09/2021 fcstdate and not considering any filters in the report.

Thanks in advance!