Offset Time Intelligence Not Working Properly

[ysherriff]

Hi all,

I am testing out how to use Offset in Time Intelligence but the output is not coming out correctly. I created three separate formulas to test. I am trying to calculate Prior Quarter. Thank you for your help and please let me know what I am doing wrong.

Image #1 - Matrix table with all three formulas.

File Access

https://1drv.ms/u/s!AlTfdvX0WzaQ1Hp_5BiThvTIDA1f?e=oidNeC

[ysherriff]

Thank you Tom. Perfect. Didn't understand the filter context within Offset.

Much appreciated

[TomMartens · Accepted solution]

Hey @ysherriff ,

try this measure:

Prev Quarter = 
CALCULATE(
    [# of MQLs]
    , ALL( 'Date'[Quarter & Year] )
    , OFFSET(
        -1
        ,
        , ORDERBY( 'Date'[QuarternYear] , ASC )
        , KEEP
    )
)

Be aware that there is an ALL that removes the existing filter.
OFFSET used as filter modifier creates a new filter context, but does not remove an existing one.

Hopefully, this provides what you are looking for.

Regards,
Tom