topic Comparing current year to previous when there is no data this year in DAX Commands and Tips

Comparing current year to previous when there is no data this year

sbrazeau — Sun, 26 May 2019 20:38:55 GMT

Hello.

I am building a set of reports for the company I work for. Almost done, I realize there is a problem. I need to compare incidents and accidents between either years or periods. Everything seemed to work fine, until I realised I am missing data once in a while. To make an easy example, my tables would look like this:

years	type of accident
2019	A
2019	A
2019	B
2019	C
2018	B
2018	B
2018	C
2018	C
2017	A
2017	C
2017	C

My reports let you select a year and it will show a table with the count of accidents for the selected year and the priopr year (SAMEPERIODLASTYEAR). So if I select 2019, I get:

years	A	B	C
2019	2	1	1
2018	0	2	2

If I select 2018, this is where I get the problem. Since there is no "A" in 2018, "A" does not get evaluated and the returned table is:

years	B	C
2018	2	2
2017	0	2

instead of:

years	A	B	C
2018	0	2	1
2017	1	0	2

Is there a function that would force the table to consider all values of the SAMEPERIODLASTYEAR and not only the current year?

Thanks!

Re: Comparing current year to previous when there is no data this year

MauriceMecowe — Mon, 27 May 2019 09:30:27 GMT

Hi sbrazeau,

Maybe the ISBLANK() function is what you're looking for. Or you can build out a query where with a filter clause. It will look something like:

[Comparison over time] :=
CALCULATE(
	[Value],
	FILTER(
		ALL('Date'),
		CONTAINS(
			VALUES, ('Date'[Date])
			'Date'[Date],
			'Date'[Date]
			)
		)
	)

Re: Comparing current year to previous when there is no data this year

sbrazeau — Mon, 27 May 2019 15:46:19 GMT

Unfortunately, I don't think the ISBLANK() would do the trick since there is no blank entry in the table. The problem is really that if my COUNTA=0 this year, the report does not care about last year.