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

[sbrazeau]

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!

[MauriceMecowe]

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

[sbrazeau]

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.