max value selected in date slicer

[Anonymous]

I have a date slicer, I need to get the max of the date select to pass it as a parameter of a dax function :

I tried :

selected_date= CALCULATE(max('Calendar'[date]);(ALLSELECTED('Calendar'))

Table= Filter (CURRENCY_RATE; CURRENCY_RATE[Date]=selected_date)

in this example:

I would like to filter the table on the last value selected (=13/06/2017)

and get only this row:

could you please help me how can I do it?

Thanks in advance

[v-danhe-msft]

Hi @Anonymous,

https://community.powerbi.com/t5/Desktop/max-value-selected-in-date-slicer/m-p/571351#M269765

Regards,

Daniel He

[v-danhe-msft · Accepted solution]

Hi @Anonymous,

If you want to create a new table and make it be dynamic with the slicer, I am afraid you could not achieve this feature currently, you could try to use the Top N filter in the visual filter to get your desired result:

You could also download the pbix file to have a view.

Regards,

Daniel He

[Ashish_Mathur]

Hi,

Assuming you have a Calendar Table and a relationship from the Date column of the Currency_Rate Table to the Date column of the Calendar Table, try this measure to get the rate on the last date of the date selected in the slicer range.  Ensure that the slicer is built from the Calendar Table

=CALCULATE(MAX(Currency_Rate[Currency_rate]),DATESBETWEEN(Calendar[Date],MAX(Calendar[Date]),MAX(Calendar[Date])))

Does this help?

[chuallanca]

What if you don't have the relationship between the calendar and that particular table ? How would you propose to get the max and min of the selectedvalue form the slicer? 

[Ashish_Mathur]

There should definitely be a Calendar Table.

[Anonymous]

The return value of the function is "currency code".

But I want to select all rows corresponding to the max value selected in "slicer date".

This DAX formula works but isn't dynamic in relation to the selected value (slicer date).

Table 2 = filter(CURRENCY_RATE; CURRENCY RATE[VALID_FROM]=DATEVALUE("30/04/2017"))

This should be dymanic : DATEVALUE("30/04/2017") 

[Ashish_Mathur]

Hi,

Does this work?

Table 2 = filter(CURRENCY_RATE; CURRENCY RATE[VALID_FROM]=MAX(Calendar[Date]))

[Anonymous]

Hi,

The issue here is that this part "MAX(Calendar[Date])" returns the most recent date (the last date in the whole Calendar range date) but not the last date selected with the slicer selection.

I have managed to get the correct date value (the last date selected with the slicer) by using a Measure but thateasure cannot be used here because it does not return the same value when used as a FilterExpression.

I understand that the underlying problem is the context of that Measure but I don't understand how to use Earlier() in that situation since you can't use that function with a Measure as a parameter.

[SinghS]

Can you please tell, what change you did to the measure to get last date selected with the slicer selection and not the max date present in the calendar date