Cumulative Running Totals for Prior Year (flat line only, want cumulative curve) in Chart

[h0ttamale]

I have a flat line instead of a cummulative curve for a prior year measure calculation (black line in graph below). I'd like to see a cummulative curve, just like the current year (green line in graph below). I'm trying to graph cumulative running count on a line graph comparing prior year to current year count.

How to I display the black line as a running total curve?

The x-axis is week number.

Chart:

Measures:

CumulativeQICY:

CumulativeQIPY:

I've tried incorporating sameperiodlastyear, dateadd and have failed.

Thanks in advance for all your help. I'm really looking forward to the responses. Thank you.

[OwenAuger · Accepted solution]

Hi @h0ttamale

The beauty of time intelligence functions is that you can calculate an existing measure in an altered date context without having to rewrite the logic of the underlying measure.

I would write CumulativeQIPY like this:

CumulativeQIPY =
CALCULATE ( [CumulativeQICY], SAMEPERIODLASTYEAR ( 'Calendar'[Date] ) )

Since CumulativeQIPY is already working correctly, wrapping it in CALCULATE with SAMEPERIODLASTYEAR performs exactly the same calculation but in a date context one year earlier.

You could also use DATEADD ( 'Calendar'[Date], -1, YEAR ) in place of SAMEPERIODLASTYEAR ( 'Calendar'[Date] ).

Regards,

Owen

[OwenAuger · Accepted solution]

Hi @h0ttamale

The beauty of time intelligence functions is that you can calculate an existing measure in an altered date context without having to rewrite the logic of the underlying measure.

I would write CumulativeQIPY like this:

CumulativeQIPY =
CALCULATE ( [CumulativeQICY], SAMEPERIODLASTYEAR ( 'Calendar'[Date] ) )

Since CumulativeQIPY is already working correctly, wrapping it in CALCULATE with SAMEPERIODLASTYEAR performs exactly the same calculation but in a date context one year earlier.

You could also use DATEADD ( 'Calendar'[Date], -1, YEAR ) in place of SAMEPERIODLASTYEAR ( 'Calendar'[Date] ).

Regards,

Owen

[h0ttamale]

That's awesome. Thanks!

Do you know why my original code wasn't working as expected? My thought was that it was because the x-axis started at Week 1 2017 and during Week 1 2017, the 2016 activities alredy took place and thus for every week after week 1 2017, the 2016 activities didn't change...

[OwenAuger · Accepted solution]

Hi @h0ttamale

Yes you're right.

In your original measure, having the condition YEAR ( 'Calendar'[Date] ) < MAX ( 'Calendar'[Year] ) meant you were effectively showing a cumulative total for all previous years, which wouldn't change within a given year (it also meant 'Calendar'[Date] <= MAX ( 'Calendar'[Date] ) was redundant.

In your visual, only 2017 was selected so your measure returned a cumulative total up to the end of 2016 for all dates.

If you had plotted multiple years, you would see the line step up to a new constant each year.

Owen 🙂