cancel
Showing results for
Did you mean:
Regular Visitor

Cumulative Totals on a Filtered Period / Item

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.

Regards

Neil

1 ACCEPTED SOLUTION
Super User

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] )
)
)
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

2 REPLIES 2
Regular Visitor

Thanks @Jihwan_Kim

That's perfect

Super User

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] )
)
)
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Announcements