Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper V
Helper V

Trouble with new OFFSET() function and ignoring Blanks

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




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. 




Super User
Super User


You may download my PBI file from here.

Hope this helps.


Ashish Mathur
Community Support
Community Support

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.

@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). 




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.

@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... 

Community Champion
Community Champion

Hi @bvy

Try this: 


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



Connect on LinkedIn

Helpful resources

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.