Trouble with new OFFSET() function and ignoring Blanks

[bvy]

I have a Data table (UnitCounts), and a Dates table. I have a measure called _avg which averages unit counts by date. I have another measure meant to return the difference of the average in the current date from the average from the previous date. It looks like this: 

_range = ABS(

   CALCULATE(   [_avg],    OFFSET(-1, , ORDERBY( Dates[Date]))   )

   -
   [_avg]

)

How do I modify the measure to IGNORE rows where either the _avg is BLANK, or the _avg from the previous row/date (given by the OFFSET function) is BLANK? The default behavior seems to be to treat those values as zero and return a value. I want it to return blank in those scenarios. More detail in the screenshot below, and a PBIX is attached also. Thank you. 

https://www.icloud.com/iclouddrive/034mSDdynrXt6DEWBGSJwvfCw#imr

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[v-jianboli-msft]

Hi @bvy ,

Please try to modify the measure _range:

_range = 
var _a = ABS(CALCULATE([_avg], OFFSET(-1, , ORDERBY(Dates[Date]))) - [_avg])
return IF(ISBLANK([_avg])||ISBLANK([_prevAvg]),BLANK(),_a)

 Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bvy]

@v-jianboli-msft  Hey that works really well, thank you. Except for one problem. When I publish to the Service, I get an unspecified error from that measure when it's tied to a period slicer that I use. See below. This is from my actual production solution, but the measure shown is equivalent to _rangeAvg in the solution here. Works fine in Desktop. And it works fine in the Service if I eliminate the OFFSET function from the underlying _range measure calculation. Makes me think there's something buggy about it since it's a new addition to DAX (Dec. 2022). 

[v-jianboli-msft]

Hi @bvy ,

This issue has been submitted internally.

Please be patient to wait for fixing. If there is any news, I will update it here.

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bvy]

@v-jianboli-msft Thank you so much! Is there a case or ticket number you can share with me? This is pretty important for our project, even though we have a workaround for now... 

[tex628]

Hi @bvy, 

Try this: 

Dynamic Offset = 
VAR DatesFiltered = FILTER( ADDCOLUMNS( ALL(Dates) , "Value" , [_avg] ) , [Value] <> BLANK())
Return
SUMX( SUMMARIZE( Dates, Dates[Date] , "Value" , CALCULATE( [_avg]  , OFFSET( -1 , DatesFiltered , ORDERBY( Dates[Date] )))), [Value] )

Br, 
J