Dynamic filter last four quarters regardless the year

[tmhalila]

Hello everyone,

Please assist. I want to present a chart that will auto-filter the last four quarters regardless of the year e.g., FY21 Q4, FY22 Q1, FY22 Q2, and FY22 Q3. I have attached the sample file below. NOTE: Quarter One starts on October

Here is the file: Power BI File 

[Nithinr · Accepted solution]

Create a measure that returns quarter difference with your max date in fact table.

use below measure in the visual filters to display number of quarters you want. 

QuartersDiff = var maximum = CALCULATE(MAX(testing[boardapprovaldate]),ALL('testing'))
RETURN
DATEDIFF(MAX(testing[boardapprovaldate]),maximum,QUARTER)

[danextian · Accepted solution]

Hi @tmhalila 

When you say last four quarters, is that based on the latest quarter in testing table or based on a slicer selection? If it is based on the slicer selection, you must use a disconnected dates table - one which doesn't have relationship to dates or testing table. Otherwise, your visual show just whatever is selected from the slicer.

First, sort your quarters first by ranking

Quarter Sort = 
RANKX (
    VALUES ( 'dim date'[fiscalquarter&year] ),
    'dim date'[fiscalquarter&year],
    ,
    ASC,
    Dense
)

Create a disconnected table (more than one ways to do it but you can reference your dates table)

dim date (disconnected) = 
'dim date'

Create a measure that will show just the last four quarters based on slicer selection

Count new =
VAR currentquartersort =
    SELECTEDVALUE ( 'dim date (disconnected)'[Quarter Sort] )
RETURN
    CALCULATE (
        [No of Project],
        FILTER (
            'dim date',
            'dim date'[Quarter Sort] >= currentquartersort - 3
                && 'dim date'[Quarter Sort] <= currentquartersort
        )
    )

Replace the column used in quarter slicer with the one from the disconnected dates table.

Please see attached pbix for details.

[tmhalila]

This is something incredible, I didn't think of it, but I see this is the best way to handle selection on the slicer. It adds more flexibility with multiple visuals.

[Nithinr · Accepted solution]

Create a measure that returns quarter difference with your max date in fact table.

use below measure in the visual filters to display number of quarters you want. 

QuartersDiff = var maximum = CALCULATE(MAX(testing[boardapprovaldate]),ALL('testing'))
RETURN
DATEDIFF(MAX(testing[boardapprovaldate]),maximum,QUARTER)

[tmhalila]

Thanks for the response! This is short and clear.