Clustered Column Chart Data Selection

[bdehning]

I have Clustered Column chart that I have set to show a maximum of 60 months of data.  I do this by using YearMonth Field is in the last 60 months.  That currently means from today back 60 months.

The issue is that users can select a year on a slicer outside the last 60 months.  If they do, this visual is blank.  

Is there a way to set up my chart and filters to allow up to 60 months of data to show and stioll allow the users to select any of the years available, even if not in past 60 months? 

[v-yaningy-msft · Accepted solution]

Hi, @bdehning 

First, thanks for @marcelsmaglhaes's and @Ashish_Mathur 's help. You can refer to their replies and if it doesn't work, refer to the following methods. Based on your description, I have created some measures to achieve the effect you are looking for. Following picture shows the effect of the display.

Put the Measure(Filter month) to the Filter Pane in the visual, and set 1 to filter.

Filter Month =
VAR _end =
    TODAY ()
VAR _start =
    EOMONTH ( _end, -60 )
VAR _result =
    IF (
        SELECTEDVALUE ( 'Table'[Date] ) >= _start
            && SELECTEDVALUE ( 'Table'[Date] ) <= _end,
        1,
        0
    )
RETURN
    _result

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Ashish_Mathur]

Hi,

I am not clear about what you want.  Do you want to show the total sales for ech of the past 60 months ended the month select by the user in the slicer.  So if a user selects Jan 2024, then there should be 60 columns in the column chart ending in Jan 2024?

[marcelsmaglhaes]

Hey @bdehning 

Yes, you can achieve this by modifying the interaction between your slicer and the clustered column chart. Here's how you can set it up:

1. Keep the slicer as it is, allowing users to select any year, even those outside the last 60 months.

2. In the clustered column chart visual, use a measure that dynamically calculates the last 60 months of data based on the selected year in the slicer.

Here's an example of how you can create such a measure in DAX:

Last_60_Months_Data =
VAR Selected_Year = SELECTEDVALUE('Your_Slicer_Table'[Year_Column])
VAR Max_Date = DATE(YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY()))
VAR Min_Date = DATE(Selected_Year - 4, 1, 1) // Assuming 60 months is 5 years

RETURN
CALCULATE(
[Your_Measure],
FILTER(
'Your_Date_Table',
'Your_Date_Table'[Date_Column] >= Min_Date &&
'Your_Date_Table'[Date_Column] <= Max_Date
)
)

Replace 'Your_Slicer_Table'[Year_Column] with the appropriate columns from your slicer table, and replace 'Your_Measure', 'Your_Date_Table', and 'Your_Date_Column' with the names of your measure, date table, and date column, respectively.

With this measure, the clustered column chart will dynamically adjust to show the last 60 months of data based on the selected year in the slicer, allowing users to select any year without making the visual blank.

[bdehning]

I have to look into how to create [Your Measure] as it comes from SQL Statement.