Show Measure for missing data point in dax

[mhsk]

Hi,

I have a fact table with data but not for all data points in calendar table, when i trow data into matrix it shows blanks when there is no corresponding value - which is ok, but i'm trying to replace blank value with my measure, please see example:

when i pass a measure:

ReplaceBlank = IF( ISBLANK(MyOtherCalc), MyCalc, MyOtherCalc )

it does not return any value. If i pass a measure: ReplaceBlank2 = IF( ISBLANK(MyOtherCalc), 0, MyOtherCalc ) it works fine and return 0 when there is a blank value in matrix:

How would i pass a measure instead of 0 in fields i do not have a data values (blanks) ?

@Greg_Deckler suggested to use isblank in othe post to receive 0 but it seems thats not a solution in my case.

[mhsk]

Thanks for promp reply @Greg_Deckler , you just realized me my measure is not returning any value as there is no value for given calendar table week so nothing to return, now i made it work with simple allexept but i see my problem is more complex:

I found now that i need to reference to previus calculated measure value, not to constant value when there is no value to show from fact table, would you advise how to build such measure (that will produce the same value that was present in previously (previous date) ? 

so what i'm receiving now is this:

and i'm expecting this:

many thanks for all your suggestions - not only in this topic, they save lives! (and a lot of time)

[Greg_Deckler]

@mhsk So, what you will need to do is to check if the value returns blank and you will need a table VAR that returns values for each date. Then you can get the MAX (MAXX) Date where that value is not blank and use the value for that date. I don't really know the specifics of your source data so it's hard to be specific. 

[mhsk]

@Greg_Deckler  yes this is the logic i would need to pass to get the result. Check if blank its easy with ISBLANK but i can overcome creating virtual table that will check what is the last non blank value/date and copy that to any blank field until new value is present in the fact table. Would you suggest any solution? .pbix file is here https://we.tl/t-fgPJcyZ3ci

[mhsk]

@Greg_Deckler  any chance you could have a look at the pbix?

what you have written: "you will need a table VAR that returns values for each date." doesnt show up in any scenario which approach shall i take? any suggestion appreciated

[Greg_Deckler]

@mhsk So, the concept would be this:

New Measure = 
  VAR __Table = SUMMARIZE('Table',[Date],"__Value",[Your measure])
RETURN
  IF(ISBLANK([Your measure]),
      VAR __Date = MAXX(FILTER(__Table,[__Value] <> BLANK(),[Date])
    RETURN
      MAXX(FILTER(__Table,[Date] = __Date),[__Value]),
    [Your measure]
  )

[Greg_Deckler]

@mhsk I can only speculate that your other measure is also returning blank. You can confirm this by creating a measure ReturnZero = 0 and then this:

ReplaceBlank = IF( ISBLANK(MyOtherCalc), ReturnZero, MyOtherCalc )

That should also return 0.