Hi,
to build My Dashboard I use imported invoice data from the last 4 years via Power Query from an Oracle databank.
One of the things I need to Show is what the current price for a particular customer is, as the price can vary up and down.
I have a feeling that I need to be using a DAX Formula but I can't figure it out.
So basically what I need to do is return the amount from the price column which corresponds to the youngest date for a particular customer ID
Any Ideas?
Hi Andrew,
It's difficult to be specific without seeing your model structure, but let's say you build a PivotTable with Customer ID in the row labels. You can write a measure something like this:
=CALCULATE(SUM(Table1[Price]), FILTER(ALL(Table1[Date]),[Date]=MAX([Date])))
See attached.
Mynda
Hi Mynda,
I changed the Measure to calculate the Max and it worked great thankyou.
Latest Price:=CALCULATE(MAX(LieferscheinData[Preis]);FILTER(ALL(LieferscheinData[Datum]);[Datum]=MAX([Datum])))
I use a German version of Excel so my Syntax sometimes looks different ie. we use ; instead of , but fortunately all of DAX is in English even in the German version, so I don't have to scratch my head translating formulas.
Glad I could help. If you're using MAX then this suggests that you have multiple prices for a single date and you're finding the maximum value for a date/period. If there's a possibility that the price goes down, then MAX will return the wrong result. Again, I don't know your data, but just thought I'd mention it in case.
