Cumulative Totals on a Filtered Period / Item

[neilcotton]

Good afternoon all.

I've been looking for a solution to this and tried many of the solutions already mentioned, but these all only seem to work with SET, framed data.

What I am trying to achieve (in my head) sounds like it should be a pretty standard requirement for most organisations.

Example:

• A sales table contains Item Number, Qty, SalesDate, SalesYearMonth
• A line chart shows the SalesYearMonth on the Axis and Cumulative Sales Qty as the Value.
• Two filters on the page, Item Number and SalesYearMonth.
• When a filter is applied, the Cumulative value should reflect whatever filter has been applied.

ItemNumber	Quantity	Sales Invoice Date	Sales Year Month
ProdA	5	2 Jan 2022	2022-01
ProdA	20	5 Jan 2022	2022-01
ProdB	10	6 Jan 2022	2022-01
ProdB	12	10 Feb 2022	2022-02
Prod A	30	15 Feb 2022	2022-02
Prod C	100	20 Feb 2022	2022-02
Prod C	25	25 Feb 2022	2022-02
Prod C	30	4 Mar 2022	2022-03
Prod A	40	7 April	2022-04

So if no filter is applied, it the line chart should look like:

Sales Year Month	Cumulative Sales Qty
2022-01	35
2022-02	35+167 = 202
2022-03	35+167+30 = 232
2022-04	35+167+30+40 = 272

However, if they selected Date Range 2022-03 to 2022-04 the chart would show

Sales Year Month	Cumulative Sales Qty
2022-03	30
2022-04	30+40 = 70

And if they just selected Product A and no date filter, the chart would show

Sales Year Month	Cumulative Sales Qty
2022-01	25
2022-02	25+30 = 55
2022-03	25+30+0 = 55
2022-04	25+30+0+40 = 95

Non of the solutions I've found regarding cumulative calculations, allows for this level of flexability.

Please help.

Regards

 Neil

[neilcotton]

Thanks @Jihwan_Kim 

That's perfect

[Jihwan_Kim · Accepted solution]

Hi,

I suggest having a calendar table like below.

Please check the below picture and the attached pbix file.

Cumulative sales qty: =
CALCULATE (
    SUM ( Data[Quantity] ),
    FILTER (
        ALLSELECTED ( 'Calendar' ),
        'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
    )
)