Show a zero instead of blank in a matrix for dates within facts table

Question

My calendar table has dates between 1-1-2000 and 31-12-2017.

However, the facts table contains data only between 1-11-2016 and 1-3-2017. In a measure, I used the the following expression to display a measure in a matrix:

Saldo em EUR = IF(ISBLANK(SUM('Table1'[Column1]));BLANK();CALCULATE( etc... ))

This measure checks if the sum of Column1 is blank and it shows a blank field in the matrix to prevent it from showing all the dates range in the calendar table.

But - It also shows a blank field for the dates within the facts table where there's no value for the sum of Column1.

I'd like it to show a zero instead of a blank field - how can I do that and still preventing the matrix from showing all the dates range in the calendar table?

Thanks in advance for helping!

You might want to modify your fact table in order to the CALCULATE(... expression returns zero instead of BLANK(). If you include your source tables we could check it. — alejandro zuleta
Without sample data, it's hard to provide an answer. But in pseudo-code you could add logic to say "If the sum of Column1 is blank AND the date is before the first date, return blank. If the sum of column1 is blank but the date is on or after the first date, return 0". A reproducible sample set of data would be helpful though. stackoverflow.com/help/mcve — Leonard

Foxan Ng Foxan Ng · Accepted Answer · 2017-03-02T08:13:12

Though I don't fully understand the first part of your question, I assume that you want the CALCULATE( etc... ) part to return 0 instead of BLANK().

There is a simple and easy trick to do so: add + 0 to your measure.

Saldo em EUR = IF(ISBLANK(SUM('Table1'[Column1]));BLANK();CALCULATE( etc... ) + 0)

Result:

Show a zero instead of blank in a matrix for dates within facts table

2 Answers