Last Sale Date and Amount per Product with Total

[jfpalumbo123]

Data sample is:

Product    Date          Amount

1               01/02       $100

1               02/15       $200

2               02/15       $300

2               03/31       $400

I am trying to get:

1 - Last Date and Amount per Product

2 - Total Amount of all items in #1

Desired output is:

Product    Date          Amount

1               02/15       $200

2               03/31       $400

                   Total:      $600

I can get #1 via many methods, but my Total row is always only showing the Amount of the last Date over all the data (or other issues) but not the desired output. ie: Total row only shows 03/31 value of $400, but I cannot get it to include the Product 1 02/15 Amount of $200 so that my total is $600 as desired.

[ValtteriN · Accepted solution]

Hi,

Here is one way to do this with a filter measure:

data:

dax:

Filter last date for product =

var _ldate = CALCULATE(MAX('Table (15)'[Date]),ALL('Table (15)'[Date])) return

IF(MAX('Table (15)'[Date])=_ldate,1,0)


Now place the measure to your visual:

Measure 25 is just a sum measure for getting the total. For total amount of items you can use count.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[jfpalumbo123]

That worked perfectly. I sure did over-engineer it 😞 

Thank you!