cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
tex628
Community Champion
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
)

 

 


Connect on LinkedIn

View solution in original post

7 REPLIES 7
tex628
Community Champion
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

Connect on LinkedIn
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 DateDateValue
Jan-1901/01/2019500
Jan-1901/02/2019900
Jan-1901/03/2019800
Jan-1901/04/2019200
Feb-1901/01/2019500
Feb-1901/02/2019900
Feb-1901/03/2019200
Feb-1901/04/2019200
Mar-1901/01/2019500
Mar-1901/02/2019900
Mar-1901/03/2019200
Mar-1901/04/2019400
Apr-1901/01/2019500
Apr-1901/02/2019900
Apr-1901/03/2019200
Apr-1901/04/2019200

 

 

tex628
Community Champion
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...


Connect on LinkedIn
Anonymous
Not applicable

Yes it is in the date format already.

tex628
Community Champion
Community Champion

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


Connect on LinkedIn
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 DateDateValueCumulative
Apr-1901/01/2019500500
Apr-1901/02/20199001400
Apr-1901/03/20192001600
Apr-1901/04/20192001800
tex628
Community Champion
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
)

 

 


Connect on LinkedIn

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors