Rolling 4 Quarters Based on Slicer Selection

[skoldier24]

Hi everyone, I've been stuggling to figure out a solution and hoping that someone in this community could point me in the right direction.

End Goal: Table/Matrix the displays only data for Last 4 Quarters based on slicer selection

• I found the following post here, and was able to implement for my situation and it is mostly working: link 
  • Summary: use a disconnected date table and a flag measure to flag the last four quarters. Then, use a visual level filter and the flag measure to filter for only for quarters with "Y" as these are the last four quarters

Here is the measure I'm using to flag the last 4 quarters:

R4 Quarter Flag =

VAR _max =

    SELECTEDVALUE ( 'Date Copy'[Quarter Index] )

VAR _min =

    CALCULATE (

        MAX ( 'Date Copy'[Quarter Index] ),

        FILTER (

            ALL ( 'Date Copy' ),

            'Date Copy'[Quarter Index]

                = SELECTEDVALUE ( 'Date Copy'[Quarter Index] ) - 3

        )

    )

VAR TimeFrame =

    MAX ('Date'[Quarter Index])

RETURN

    IF ( TimeFrame <= _max && TimeFrame >= _min, "Y", "N" )

Issue: This approach doesn't seem to work when there are rows added to the matrix from other tables - ie a location table. 

• See attached pbi file for example of the issue: pbi example - google drive 
• The first matrix shows the R4 quarters, which is how this should work
• The second matrix includes the location code, which causes the R4 quarter flag filter to no longer work
• I imagine that there is something in the DAX of the R4 quarter flag that is cuasing the rows to override the column filter, however, I am not able to figure out what is causing this

It would be so appreciated if anyone is able to provide any suggestions how to make this work when there is row data as well. Thank you in advance for your help!

[Rupak_bi]

Just modify your if statement as below. 

RETURN

    IF ( TimeFrame <= _max && TimeFrame >= _min, "Y", Blank() )

[skoldier24]

Does anyone else have any thoughts on this? Would love any input!

[Rupak_bi]

Looks like you need to use "all except( table, location) instead of all ( table). Just try and let me know. 

[skoldier24]

Hi Rupak, thanks for your response. I tried using ALLEXCEPT instead of ALL and I couldn't get to work on my end. I am maybe using wrong - did this work for you in the example pbi file? Thanks again, truly appreciate any help!

[Rupak_bi]

is it OK

[skoldier24]

That looks right! Could you please post the DAX? thank you