Bar chart with latest values based on slicer

[Msag]

I am trying to plot in a bar chart the latest price for each product. I would like that bar chart to change as the user changes the latest date through a slicer.

Here is my data, the table is called "PriceLog"

Product	Price	Date
A	37.1	21/12/2022
B	88.9	21/12/2022
A	89.9	18/12/2022
E	50.0	17/12/2022
B	49.0	15/12/2022
C	63.2	10/12/2022
D	5.7	08/12/2022
A	54.9	05/12/2022
F	40.2	22/11/2022
H	31.1	05/11/2022

When the slicer is set with the max date at 21/12/2022, I would like to have this graph plotted

	21-Dec
A	37.1
B	88.9
C	63.2
D	5.7
E	50
F	40.2
H	31.1

and when it is set to 15/12/2022

	15-Dec
A	54
B	88.9
C	63.2
D	5.7
E	0
F	40.2
H	31.1

I tried creating a calculated value using DAX:

lastPrice = 
VAR _tbl = ALLSELECTED(PriceLog)
VAR _maxDate = MAXX(_tbl, PriceLog[Date])
 RETURN CALCULATE(SELECTEDVALUE(PriceLog[Price]), FILTER(_tbl,PriceLog[Date]=_maxDate))

But it did not do the trick.

It really feels like I am missing something important in the way I am doing things. Shoud I create another table? If so, how does it update as the user changes the max date through the slicer?

Thank you very much for your help!

[Msag]

@amitchandak 

Thank you very much for your answer. Fun fact, I already saw your post on medium! I thought that your solution and what I was doing was similar.... but after trying it, I was apparently completely wrong as yours seems to be working... but I still do not understand the difference between both DAX

This is the working solution (yours):

latestPrice = 
var _max = maxx(filter(ALLSELECTED(PriceLog), PriceLog[Date] = MAX((PriceLog[Date]))),PriceLog[Date]) 
return 
CALCULATE(max(PriceLog[Price]), filter((PriceLog) ,  PriceLog[Date] = _max))

 so "_max"' stores the latest date. This line makes less sense to me as I don't have to handle an ID as you did in your tutorial. Is this line here just to get the latest value from the slicer?

As for the CALCULATE section, I do not understant why "PriceLog[Date] = _max" works.

For example if I take this table again:

Product	Price	Date
A	37.1	21/12/2022
B	88.9	21/12/2022
A	89.9	18/12/2022
E	50.0	17/12/2022
B	49.0	15/12/2022
C	63.2	10/12/2022
D	5.7	08/12/2022
A	54.9	05/12/2022
F	40.2	22/11/2022
H	31.1	05/11/2022

If I set the date value to now 09/03/2023, is this going to be the value stored in _max? If so, since I have no product at that date, how is it grabbing the latest value still dated 21/12/2022 ?

And if I go back to my original try:

lastPrice = 
VAR _tbl = ALLSELECTED(PriceLog)
VAR _maxDate = MAXX(_tbl, PriceLog[Date])
 RETURN CALCULATE(SELECTEDVALUE(PriceLog[Price]), FILTER(_tbl,PriceLog[Date]=_maxDate))

How is this one any different? When using this one, I also have the ALLSELECTED and the _max. But CALCULATE only returns the price value for the latest date, regardless of the product. So by setting the max date value again at 09/03/2023, it would return the maximum latest value (product B, price = 88.9), and assign this value to all product in the chart, though I don't understand how this one is different to your solution.

If you find the time (and motivation) to answer my questions that would be tremandously helpful. In the mean time, thank you so much for all your videos and tutorials, they have already helped me a lot in my journey learning about PBI 🙂 

[amitchandak · Accepted solution]

@Msag , Refer if these two can help

Latest
https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0

https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee8...