Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
BI_Enthusiast
Frequent Visitor

Dynamic bar chart that shows last 4 months of available data

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:

BI_Enthusiast_0-1717118630780.png

 

 


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:

 

Capture 2.PNG

 

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:

 

BI_Enthusiast_2-1717118630772.png

 

 

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:


 

Capture 4.PNG 

 

 

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

1 ACCEPTED SOLUTION
TomMartens
Super User
Super User

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
image.png
Just use the measure on the visual instead of the [%_Yes] measure.

Hopefully this provides what you are looking for.

 

Regards,
Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

5 REPLIES 5
TomMartens
Super User
Super User

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
image.png
Just use the measure on the visual instead of the [%_Yes] measure.

Hopefully this provides what you are looking for.

 

Regards,
Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Hi @TomMartens ,

You are a Genious!!!!

Thank you so much for your spupport on this.


 

 

Kind regards,

Ash.

My pleasure!



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
TomMartens
Super User
Super User

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:

image.png

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

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Hi @TomMartens ,

Thank you so much for your response. 

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

Capture 5.PNG

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.