Calculation based on latest submitted data

[Anonymous]

Here is an example of the table I have 

Products	Sales	Reporting Date	Submitted Date
5	147	10/19/2018	11/16/2018
4	985	11/11/2018	12/5/2018
1	411	12/1/2018	1/3/2019
10	188	12/5/2018	1/15/2019
6	564	12/19/2018	1/18/2019
2	384	1/15/2019	2/25/2019

The calculation I want is Rate = ([Products] / [Sales]) * 1000

But I want it only for the latest submitted data. Currently I have a date slicer set up on [Reporting Month]. An example of what I want to happen is if the date slicer is set to December 2018 then I want the calculated rate to be from the data submitted on 1/18/2019 or (6/564)*1000. 

I tried a calculation with LASTDATE but it seems like it is just finding the lastdate in the whole column and does not change with the slicer. 

For example a measure that just sums the latest available products does not move with a slicer so i think it is wrong.

My measure = CALCULATE(SUM('TableI'[Products]),LASTDATE('Table'[Submitted]))

thanks for any help

[VasTg · Accepted solution]

@Anonymous 

Try this DAX...

Measure = 
VAR MAX_SUBMITTED_DATE = MAX('Table'[Submitted Date])
RETURN CALCULATE((SUM('Table'[Products])/SUM('Table'[Sales]))*1000,FILTER('Table','Table'[Submitted Date]=MAX_SUBMITTED_DATE))

I have a seperate Dates table and i have defined relationshiop based on date and reporting date. Year and Month filters are from Dates table. The Measure shows expected output and the table shows your sample data for the selected Year and Month.

 

If it helps, mark it as a solution

Kudos are nice too

[Anonymous]

This works perfectly. Thank you so much!