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
