Interactive graph using a measure and a date slicer.
I am making a raport which analyses the progress of the MSCI index of a list of countries. I would like to have a graph with the MTD of the countries which perform worst at a certain date. The dates in the dataframe are non consecutive. For example, to show a graph consising just the top five worst perfoming countries by the end of august, i used this DAX.
MTD end aug count DM = VAR aug = CALCULATE ( MAX ( 'Country DM'[Date] ), FILTER ( 'Country DM', 'Country DM'[Date] <= DATE(2021,8,31) ) )
Then i put this DAX in the filter of the graph and selected bottum 5. This works fine, this way you can see the progess of these countries throughout the whole available time period which looks like this:
However, i want the user to be able to select the date of which he/she wants to see the worst performing countries.
I have tried to include a date slicer and use this DAX;
MTD end month count DM = VAR month = CALCULATE ( MAX ( 'Country DM'[Date] ), FILTER ( 'Country DM', 'Country DM'[Date] <= SELECTEDVALUE('Country DM'[Date]) ) )
Then i put this meassure in the filter for visual and select the bottum 6. Using this, I can create a table with worst performing countries based on the selected dat in the slicer. However The graph remains emtpy. I think this has something the do with the fact that the date is on the x asis of the graph but i am not quite sure. Does someone know if its possible to make this graph interactive?
@KarianneKies Hi, though I am not 100% sure before see your report, I guess (from your explanation) that you put the [date] as outer fitrer so that user can select specific date. in that case, power BI can see only data for the selected date (1 day) among whole table.