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 curre...
Need Help
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