Forum Discussion
Calculation based on latest submitted data
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.
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
2 Replies
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