Forum Discussion

ysherriff's avatar
ysherriff
Resolver II
3 years ago
Solved

Offset Time Intelligence Not Working Properly

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

 

 

Need Help
  • TomMartens's avatar
    TomMartens
    3 years ago

    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 

2 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    3 years ago

    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 

  • ysherriff's avatar
    ysherriff
    Resolver II
    3 years ago

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

     

    Much appreciated