Search code examples
powerbidaxpowerquerypowerbi-desktop

Slicer filtered column with missing value in YTD

I have:

table

Sex Dim

Relation

YTD values with a date slicer shows:

enter image description here

But I like to see the Female value 30 on the matrix.

Solution

  • Without a date table in your model, you'll need a measure similar to this:

    Value YTD = 
  var sDate = CALCULATE( LASTDATE(Sheet1[Date]), REMOVEFILTERS('Sex') )
  var result = TOTALYTD( SUM('Sheet1'[Value]), ALL('Sheet1'[Date]), 'Sheet1'[Date] <= sDate)
  RETURN COALESCE(result, 0)