cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Anonymous
Not applicable

Cumulative Sum with filter

I am trying to do a cumulative measure to add to a chart, however becuase my data structure consist of stacked monthly reports if I do so using the below code I end up with summing over repeated instances as each ID appears more than once, therefore I need to filter on the most recent report date - how would I adapt the code below to reflect this?

Cumulative Quantity :=
CALCULATE (
SUM ( Transactions[Quantity] ),
FILTER (
ALL ( 'Date'[Date] ),
'Date'[Date] <= MAX ( 'Date'[Date] )
)
)

1 ACCEPTED SOLUTION
Community Champion

Ohh now I understand! Try this! 🙂

Cumulative Quantity :=
VAR _date = SELECTEDVALUE('Table'[Date] )
VAR _period = SELECTEDVALUE('Table'[Report Date]) Return CALCULATE ( SUM ( Transactions[Quantity] ), ALL ( 'Table' ), 'Table'[Date] <= _date,
'Table'[Report Date] = _period
)

7 REPLIES 7
Community Champion

Try this:

Cumulative Quantity :=
VAR Mdate = MAX ( 'Date'[Date] )
Return
CALCULATE (
SUM ( Transactions[Quantity] ),
FILTER (
ALL ( 'Date'[Date] ),
'Date'[Date] <= Mdate    )
)
Br,
Johannes

Anonymous
Not applicable

Thanks @tex628

I'm not sure this will work as the stack reports have the same date line duplicated in each - I have a column [report date] that I want to filter to be the max. I have included some sample data below, where the aim is to get the cumulative for each month for the April Report.

 Report Date Date Value Jan-19 01/01/2019 500 Jan-19 01/02/2019 900 Jan-19 01/03/2019 800 Jan-19 01/04/2019 200 Feb-19 01/01/2019 500 Feb-19 01/02/2019 900 Feb-19 01/03/2019 200 Feb-19 01/04/2019 200 Mar-19 01/01/2019 500 Mar-19 01/02/2019 900 Mar-19 01/03/2019 200 Mar-19 01/04/2019 400 Apr-19 01/01/2019 500 Apr-19 01/02/2019 900 Apr-19 01/03/2019 200 Apr-19 01/04/2019 200

Community Champion

Can you convert the report date column into a proper date column and use that instead?

Jan-19 to 01/01/19
Feb-19 to 01/02/19
Etc...

Anonymous
Not applicable

Yes it is in the date format already.

Community Champion

Relationship is between 'Table'[Date] and 'Date'[Date] and has to stay that way?

Anonymous
Not applicable

I'm not sure I follow? I don't think the relationship matters.

Below is an example of where I hope to get to using the above data, the cumulative for the latest report (Apr-19), as a measure not column.

 Report Date Date Value Cumulative Apr-19 01/01/2019 500 500 Apr-19 01/02/2019 900 1400 Apr-19 01/03/2019 200 1600 Apr-19 01/04/2019 200 1800
Community Champion

Ohh now I understand! Try this! 🙂

Cumulative Quantity :=
VAR _date = SELECTEDVALUE('Table'[Date] )
VAR _period = SELECTEDVALUE('Table'[Report Date]) Return CALCULATE ( SUM ( Transactions[Quantity] ), ALL ( 'Table' ), 'Table'[Date] <= _date,
'Table'[Report Date] = _period
)

Announcements

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors