Dynamic bar chart that shows last 4 months of available data

[BI_Enthusiast]

Hi Folks,

I need your urgent help where I could show last 4 months of available data on a bar chart after selecting the values from the filters.

Given below are the 4 scenarios to make the bar chart dyanmic:

Scenario 1

For Instance when "Africa" is selected from the "Continent" filter then we get to see data from the month of May 2024, March 2024, January 2024 and November 2023 as shown in the picture below:

Scenario 2

For instance when "Nigeria" is selected from the "Sports Origin" filter we get to see data from the month of May 2024, March 2024, January 2024 and November 2023 as shown in the picture below:

Scenario 3

For instance when "Dambe" is selected from the "Sports" filter we get to see data from the month of May 2024, March 2024, January 2024 and November 2023 as shown in the picture below:

Scenario 4

For instance when "John" is selected from the "Player" filter we get to see data from the month of May 2024, March 2024, January 2024 and November 2023 as shown in the picture below:

 

Here is link to access the PowerBI file and the dataset

Please let me know if anyone has any questions.

Thank you so much in advance!

Kind regards,

Ash

[TomMartens · Accepted solution]

Hey @BI_Enthusiast ,

this measure

Measure = 
var theLastNMonths = 4
var theMinimumMonthFromtheLastXMonth = 
    MINX(
        TOPN( theLastNMonths
            , SUMMARIZE(
                ADDCOLUMNS(
                    SUMMARIZE(
                        ALLSELECTED( 'Response Data')
                        , 'Calendar'[Date]    
                    )
                    , "YearMonthNumber" , INT( VALUE( FORMAT( 'Calendar'[Date], "YYYYMM" ) ) )
                )
                , [YearMonthNumber]
            )
            , [YearMonthNumber]
            , DESC
        )
        , [YearMonthNumber]
    )
var currentDate = CALCULATE( MAX( 'Calendar'[Date] ) )
var currentYearMonth = 
    IF( ISBLANK( currentDate )
        , BLANK()
        , INT( VALUE( FORMAT( currentDate, "YYYYMM" ) ) )
    )
return
IF(
    currentYearMonth < theMinimumMonthFromtheLastXMonth
    , BLANK()
    ,  [%_Yes]
)

identifies the "smallest" month of the last four months with data available: smallestOfN

Then it checks if the current month on the axis is smaller than smallestOfN, if this is the case, the measure returns BLANK(); otherwise, the original measure. Because blank values are not visualized by default September 2023 is "hidden" when Africa is selected

Just use the measure on the visual instead of the [%_Yes] measure.

Hopefully this provides what you are looking for.

Regards,
Tom

[BI_Enthusiast]

Hi @TomMartens ,

You are a Genious!!!!

Thank you so much for your spupport on this.

Kind regards,

Ash.

[TomMartens]

My pleasure!

[TomMartens]

Hey @BI_Enthusiast ,

Indeed, I have an additional question: no matter the data, you only want to show the last 4 months of data, right?

If nothing is selected from the slicer, instead of this chart:

you only want to see 4 columns, is my understanding correct?

Regards,

Tom

[BI_Enthusiast]

Hi @TomMartens ,

Thank you so much for your response. 

Yes when nothing is selected then show last 4 months of the available data.