Forum Discussion
Solved
Use the most recent values based upon a date
Hi - I am looking to create a measure to do the following:
1. Obtain the most recent values based upon the most recent date (if the Actual column is not null).
2. Upon obtaining the most recent values, perform a calculation.
Example Table:
TABLE1234
| Date | Actual | Target |
| 2025-01 | 5 | 6 |
| 2025-02 | 3 | 8 |
| 2025-03 | 8 | 10 |
| 2025-04 | 12 | |
| 2025-05 | 15 | |
| 2025-06 | 17 |
The result would be for the Date containing the most recent 'Actual Value'
Therefore the measure would use 2025-03
Actual = 8
Target = 10
Once the two values are known, then divide the Actual / Target.
Any thougths ? Jerry
Measure = var a = TOPN(1,Filter(ALLSELECTED(Table1234),[Actual]<>BLANK(),[Date],DESC) return divide(sumx(a,[Actual]),sumx(a,[Target]))
4 Replies
Like this ?
Measure = var d = max(Table1234[Date]) var a = TOPN(1,Filter(ALLSELECTED(Table1234),[Actual]<>BLANK() && [Date]<=d),[Date],DESC) return divide(sumx(a,[Actual]),sumx(a,[Target]))Measure = var a = TOPN(1,Filter(ALLSELECTED(Table1234),[Actual]<>BLANK(),[Date],DESC) return divide(sumx(a,[Actual]),sumx(a,[Target]))
