Get last 4 months period of data on a bar chart from the selected value in a filter

[BI_Data_Analyst]

Hello Folks,

I need your urgent help on the dax where I get to see last 4 Period (months) of data in a bar chart

For example.

If I choose "Football" from the "Game" slicer then I get to see data from February 2024 to May 2024.

But If I choose "Snooker" from the Game slicer  I get to see only March 2024 & May 2024 Data

"I would like to get exactly like explained below"

Whereas, when I choose "snooker" from the Game Slicer I would like to see July 2023, August 2023, March 2024 & May 2024 data becuase there is no data in the month of February 2024 and April 2024.

The DAX measure that I have used is this:

%_Yes_4_Months =

VAR _maxdate = MAX('Calendar'[Date])

VAR _lastperiods = DATESINPERIOD(New_Calendar[Date],

_maxdate, -4, month)

Return

calculate(

    [%_Yes],

    REMOVEFILTERS('Calendar'[Date]),

    KEEPFILTERS(_lastperiods),

    USERELATIONSHIP('Calendar'[Date], New_Calendar[Date]))

Please urgently help me on getting this right.

Here is the link to access the pbix file and dataset

[v-heq-msft · Accepted solution]

Hi @BI_Data_Analyst ,
Here some steps that I want to share, you can check them if they suitable for your requirement.
Here is my test data:

Create  COLUMN in power query

=Date.ToText([Date],[Format = "yyyyMM"])

Create a calculated column

Rank = 
RANKX(
    CALCULATETABLE(
        'Table',
        ALLEXCEPT(
            'Table',
            'Table'[Game]
        )
    ),
    [Custom],,
    ASC,
    Dense
)

Create measures

Measure = 
VAR _maxRank = 
CALCULATE(
    MAX('Table'[Rank]),
    ALLEXCEPT('Table','Table'[Game])
)
RETURN
IF(
    SELECTEDVALUE('Table'[Rank]) > _maxRank - 4,
    1,
    0
)

Divide = 
DIVIDE(
    CALCULATE(
        COUNTROWS('Table'),
        FILTER(
            'Table',
            'Table'[Response] = "Yes"
        )
    ),
    COUNTROWS('Table')
)

Use measure as filter on this visual

Fianl output

Best regards,

Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[BI_Data_Analyst]

Hi @v-heq-msft ,

Thank you so much for your support.

(1)Just wondering if we can select "Ireland" from "Game Origin" filter and yet we are able to see Baseball Chart.
(Because Baseball game is a part of Ireland)

(2) and when nothing is selected from any of the filters we get last 4 months "Divide" of the overall data.

Kind regards,

Ash.