Forum Discussion

bdehning's avatar
bdehning
Post Prodigy
2 years ago
Solved

Clustered Column Chart Data Selection

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.  ...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

marcelsmaglhaes's avatar
marcelsmaglhaes
Super User
2 years ago

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's avatar
bdehning
Post Prodigy
2 years ago

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