topic Re: Rolling 4 Quarters Based on Slicer Selection in DAX Commands and Tips

Rolling 4 Quarters Based on Slicer Selection

skoldier24 — Fri, 10 Nov 2023 16:14:51 GMT

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!

Re: Rolling 4 Quarters Based on Slicer Selection

Rupak_bi — Sat, 11 Nov 2023 05:37:52 GMT

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

Re: Rolling 4 Quarters Based on Slicer Selection

skoldier24 — Sat, 11 Nov 2023 19:16:35 GMT

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!

Re: Rolling 4 Quarters Based on Slicer Selection

skoldier24 — Thu, 16 Nov 2023 17:25:12 GMT

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

Re: Rolling 4 Quarters Based on Slicer Selection

Rupak_bi — Fri, 17 Nov 2023 13:07:01 GMT

is it OK

Re: Rolling 4 Quarters Based on Slicer Selection

skoldier24 — Fri, 17 Nov 2023 16:51:20 GMT

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

Re: Rolling 4 Quarters Based on Slicer Selection

Rupak_bi — Fri, 17 Nov 2023 20:03:56 GMT

Just modify your if statement as below.

RETURN

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