Zeroes instead of blanks with relative date filter

0
votes

I'm trying to figure out a way to display an aggregate measure by calendar quarter (a simple count). In the matrix I put a relative date filter (Release Date, is in the last, 1, year). So it should only show in the column headers 2019 Q4, 2020 Q1, 2020 Q2, and 2020 Q3.

I then use a measure to calculate the count and have the return statement with the condition IF(ISBLANK("MEASURE"),0,"MEASURE") in an attempt for power bi to return a zero for those quarters where no data is present.

But the result of this is that my matrix presents ALL quarter going back to 2018 Q1. enter image description here

Is there a way to show zeroes in just the quarters present in the last year (relative to todays date)?

This is what I get with a "simple" measure (note that the relative date filter applies).

enter image description here

What I want to achieve is that for supplier #1 2019 Q4 shows 0, and for 2020 Q1 and Q3 show also the zeroes. Supplier # 2 would show zeroes for 2020 Q1, Q2, and Q3.

Juan

Edit after using the date dimension table:

Now the output looks like this

enter image description here

But I'm having to use columns from my date table and not the date field from the relationship of my fact table to date table.

enter image description here

1
powerbidax
Do you have a separate calendar table? – Alexis Olson

1 Answers

0
votes

Assuming you have a separate calendar table, you need to put the relative date filter on your calendar table, not your data table.

Also, instead of using IF in your measure like

Measure =
IF ( ISBLANK ( COUNT ( Table1[Col1] ) ), 0, COUNT ( Table1[Col1] ) )

since BLANK() + 0 = 0 you can just write

Measure = COUNT ( Table1[Col1] ) + 0