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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support Team
Community Support Team

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.
1 REPLY 1
Highlighted
Community Support Team
Community Support Team

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.

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 331 members 3,447 guests
Please welcome our newest community members: