Issue while converting Blanks to 0 in Matrix

[sdobi05]

Hi All,

I have two datasets : Table 1 & Table 2 by joining on Desig to get all the desig values and represent the data in Matrix. All Desig may not come from Table1 for a particular date so, we are maintain the Required Desig in table2 and by joining with table2 will get all the Desig's along with it implementing the custom sort as well.

Here, I have to calculate the delta of stage1/stage2/Stage3 from 1/2/2020 - stage1/stage2/Stage3 of 1/1/2020.

Due to the blanks in Stage3 in 1/1/2020, the negation/subtraction is not happening correctly. I tried to make the 0 using ISBlank with if condition, still nothing is happening

IF(ISBLANK([Column1]),0,[Column1]).

Column1 : is having the numeric value goes to Values in Matrix.

I tried to make 0 in delta calculations, but it is also not working.

I would like to get 0 instead of Blanks to calculations/ Or any another proposed solution to resolve this issue .

Thanks in advance.

[v-kelly-msft · Accepted solution]

Hi  @sdobi05 ,

Based on your data,create a measure as below:

Measure = 
var _countonJan1=CALCULATE(MAX('Table1'[Count]),FILTER(ALL(Table1),'Table1'[Date]=DATE(2020,1,1)&&'Table1'[Design]=MAX('Table1'[Design])))
var _countonFeb1=CALCULATE(MAX('Table1'[Count]),FILTER(ALL(Table1),'Table1'[Date]=DATE(2020,2,1)&&'Table1'[Design]=MAX('Table1'[Design])))
Return
_countonFeb1 -_countonJan1

Then put in a matrix you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak]

@sdobi05 , you can do this month - last month

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))+0
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))+0
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))+0
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))+0
previous month value =  CALCULATE(sum('table'[total hours value]),previousmonth('Date'[Date]))+0

diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])