cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
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.

View solution in original post

1 REPLY 1
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.

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 100 members 1,406 guests
Please welcome our newest community members: