Reply
Highlighted
Frequent Visitor
Posts: 9
Registered: ‎01-26-2018
Accepted Solution

Get the corresponding value for the max date based on slicer -

Hi all,

 

This might be very easy, but I'm looking at it for so long, that I feel hopeless. I have been playing with ALLSELECTED, ALLEXCEPT, CALCULATE, FILTER, SELECTEDVALUES, VALUE, LOOKUPVALUE, MAX, MAXX... and others, but I just can;t seem to get what I want.

 

Situation:

I have a table with a few fields, but only 3 relevant for my reporting : Fund, Price, Date.

There are a few different 'funds' in the table, and for each one, the 'price' might change on a daily basis. Basically there is a row created per fund / per day.

 

What do I need?

I need to get the Price per Fund for the latest date. I also need to have the ability to calculate this for different dates. I have a slicer , the user can select different dates (from slicer visual) and the value should change accordingly.

 

Important note: The date slicer is set to 'Before'. I need the slicer to be set this way as I use the dates for other formulas where I have to add up all value up to a specific date. It's also very easy to use as it pops out a little calendar and the user can just click on a date. When the user selects a date, both (the value I need here) and the other measures I have created have to be filtered based on the same date.

 

How can you help?

Help me figure out a formula that I can use to get around this. I basically need to show, by fund, the corresponding value for the maxmium date in the date selection.

 

Examples

Let's say we have  this dataset in Table1:

 

Fund | Price | Date

Fund1 | £1 | 09 Sep 2018

Fund 2| £2 | 09 Sep 2018

Fund 1| £2.5 | 15 Sep 2018

FUnd 2 | £3 | 20 Sep 2018

 

 

User selects from slicer '09 sep 2018'. The output should be:

Fund1 | £1

Fund 2| £2

 

User selects from slicer '15 sep 2018'. The output should be:

Fund1 | £2.5

Fund 2| £2

 

User selects from slicer '21 sep 2018'. The output should be:

Fund 1| £2.5

FUnd 2 | £3


Accepted Solutions
Community Support Team
Posts: 2,979
Registered: ‎02-06-2018

Re: Get the corresponding value for the max date based on slicer -

Hi @CristinaLia,

 

Please try the measure below.

 

Measure = CALCULATE(MAX('Table1'[Price]),ALLEXCEPT(Table1,Table1[Fund],Table1[Date]))

Here is your desired output.

 

Untitled.png

Hope this can help you!

 

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post


All Replies
Community Support Team
Posts: 2,979
Registered: ‎02-06-2018

Re: Get the corresponding value for the max date based on slicer -

Hi @CristinaLia,

 

Please try the measure below.

 

Measure = CALCULATE(MAX('Table1'[Price]),ALLEXCEPT(Table1,Table1[Fund],Table1[Date]))

Here is your desired output.

 

Untitled.png

Hope this can help you!

 

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.