cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KarianneKies
Frequent Visitor

Interactive graph using a measure and a date slicer.

Hi! 

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)
)
)

RETURN

CALCULATE(SUM('Country DM'[MTD]), 'Country DM'[Date] = aug)

 

 

 

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:
 
Graph.png
 
 
 
 
 
 
 
 
 
 
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])
)
)

RETURN

CALCULATE(SUM('Country DM'[MTD]), 'Country DM'[Date] = month)

 
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?
 
Many thanks in advance!
 
Karianne 
1 REPLY 1
colacan
Resolver II
Resolver II

@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.

 

Please mark it as soluntion if helped.

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors