Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Issue while converting Blanks to 0 in Matrix

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 a...
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    Hi  Anonymous ,

     

    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's avatar
amitchandak
Super User
6 years ago

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