topic Re: Cumulative Sum with filter in Desktop

Cumulative Sum with filter

Anonymous — Fri, 12 Apr 2019 11:13:59 GMT

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

Re: Cumulative Sum with filter

tex628 — Fri, 12 Apr 2019 11:33:40 GMT

Try this:

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

Br,
Johannes

Re: Cumulative Sum with filter

Anonymous — Fri, 12 Apr 2019 11:57:11 GMT

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

Re: Cumulative Sum with filter

tex628 — Fri, 12 Apr 2019 12:00:41 GMT

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...

Re: Cumulative Sum with filter

Anonymous — Fri, 12 Apr 2019 12:10:24 GMT

Yes it is in the date format already.

Re: Cumulative Sum with filter

tex628 — Fri, 12 Apr 2019 12:14:56 GMT

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

Re: Cumulative Sum with filter

Anonymous — Fri, 12 Apr 2019 12:29:07 GMT

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

Re: Cumulative Sum with filter

tex628 — Fri, 12 Apr 2019 12:45:17 GMT

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
)