Year over year running total

[SamTrexler]

I am under a tight deadline to demo to a customer, trying to show analysis based on a current SSRS report. I'm using the Running Total quick measure, but can't seem to emulate what SSRS is doing.

Here's the existing SSRS report, and what I've been able to do with the quick measure:

As you can see, they want the running total and percentage of change through the end of 2015, even though this year's data ends in August. I see lots of examples of how to make a running total stop when there's no more data (many priot to the introduction of the quick measure), but none that show how to make it continue! 🙂 I've tried lots of options, but I can't find one that gives what they currently have.

Here's my measure for 2015:

Total VIN Count 2 Years Ago = CALCULATE([Total VIN Count],DATESBETWEEN(RPT_Tests_Summary[KeyDate],"1/1/2015","12/31/2015"))

I used DATESBETWEEN to get all of the 2015 dates, not just those up to the current date in 2017. And that gets the counts through the end of the year.

Here's the running total quick measure:

Total VIN Count 2 Years Ago running total in Month = 
CALCULATE(
	'RPT_Tests_Summary'[Total VIN Count 2 Years Ago],
	FILTER(
		CALCULATETABLE(
			SUMMARIZE(
				'RPT_Tests_Summary',
				'RPT_Tests_Summary'[KeyDate].[MonthNo],
				'RPT_Tests_Summary'[KeyDate].[Month]
			),
			ALLSELECTED('RPT_Tests_Summary')
		),
		ISONORAFTER(
			'RPT_Tests_Summary'[KeyDate].[MonthNo], MAX('RPT_Tests_Summary'[KeyDate].[MonthNo]), DESC,
			'RPT_Tests_Summary'[KeyDate].[Month], MAX('RPT_Tests_Summary'[KeyDate].[Month]), DESC
		)
	)
)

Apparently, I need to change the dates in the ISONORAFTER function to get it to continue, but I can't find a way that gives what I need.

Can anyone show me the way?

Thanks in advance.

Sam

[TomMartens]

Hey,

this looks somewhat odd to me.

Are you aware of this site

daxpatterns.com/time-patterns

almost everything that is been necessary to know about date related calculations is been said here.

Maybe you also have to consider, to create a separate Calendar table,

Hope this gets you started

Regards

Tom

[SamTrexler]

@TomMartens, thanks for replying. I've reviewed much of the stuff from SQL BI, plus searching Chris Webb, Matt Allington and RadACad. I've tried creating a calendar table based on their posts, but joining it to my table removes all time intelligence and the existing Power BI-deafult hierarchy on my date field. I even tried Marco Russo's 7/22/17 post about adding a dummy MarkAsDateTable, but that didn't help.

I'm pretty lost at this point, with almost no time left before the demo. Can anyone point me in the right direction to get me started, without a steep learning curve? An example would be helpful.

Thanks in advance.

Sam

[TomMartens]

Hey,

please create sample data and provide a pbix file and upload that file to onedrive or dropbox and share the link.

Regards

Tom

[SamTrexler]

@TomMartens, I appreciate your help. It will take me a little while to create a small, cleansed subset of the data. I'll post it as soon as I can.

Thanks again.

[SamTrexler · Accepted solution]

@TomMartens,

By creating a subset and working with a limited set of data, I think I found the problem.

This page is in a report that contains 14 years of historical data, but there's a report level filter set to normally only show the current year. With that filter set, the running totals for both 2015 and 2017 stop at the end of the 2017 data in August. With that filter cleared, the running total for 2015 goes through the end of the year and the 2017 running total goes through the end of the data. That's exactly what I want. And I now understand why, I think, based on the context. 

It also led me to an obvious bug on my part - I had erroneously used the measure Total Count to be the count this year, which seemed to work because of the report level filter. I've now created a filtered measure for the value this year, as I should have in the first place.

Now it's working as it should, but I will need to move it to a separate report. The existing report must show results on all pages for a consistent range of dates - hence the report-level filter. These visuals are separate management reports and need to show results over 2 years. Unfortunately, with them in a separate report I won't be adble to use drillthrough to show the underlying data. I'll have to figure something out for that.

Thanks for your help.

Sam

[TomMartens]

Don't forget to flag your last post as answer, this may help others.

Cheers