Forum Discussion

bvy's avatar
bvy
Icon for Helper V rankHelper V
3 years ago

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

   -
   [_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

 

Need Help

6 Replies

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    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's avatar
      bvy
      Icon for Helper V rankHelper V
      3 years ago

      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's avatar
        v-jianboli-msft
        Icon for Community Support rankCommunity Support
        3 years ago

        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.

  • tex628's avatar
    tex628
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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