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

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

Hi @CristinaLia,

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

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

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

Hi @CristinaLia,

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

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.

Announcements

#### Challenge: Can You Solve These?

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

#### Community News & Announcements

Get your latest community news and announcements.

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

#### Win Power BI Swag with Community Kudopalooza!

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

Top Kudoed Authors
Users Online
Currently online: 100 members 1,406 guests
Recent signins: