Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
So, I'm currently having an issue as described below:
Company | Product | Amount | Date |
1 | 15 | 1500 | 2022-01-01 |
1 | 16 | 2000 | 2022-02-01 |
2 | 15 | 2000 | 2022-01-15 |
2 | 16 | 3000 | 2022-02-15 |
With that in mind, I need a measure which gets the product amount on the last date available. Example, if I select the february month on slicers it should show the amount 2000 (since there's no data for february, it gets the last date prior to february) for the product 15 and 3000 for the product 16.
Solved! Go to Solution.
For that, you need a separate Date/Calendar table in your model with a 1:M relationship to the Date column in your table. You can then use a measure like this one to get your result. Replact T3Dates and T3 with your Calendar table and original table, respectively.
Latest =
VAR maxdate =
MAX ( T3Dates[Date] )
RETURN
CALCULATE (
LASTNONBLANKVALUE ( T3[Date], SUM ( T3[Amount] ) ),
REMOVEFILTERS ( T3Dates ),
T3Dates[Date] <= maxdate
)
Pat
For that, you need a separate Date/Calendar table in your model with a 1:M relationship to the Date column in your table. You can then use a measure like this one to get your result. Replact T3Dates and T3 with your Calendar table and original table, respectively.
Latest =
VAR maxdate =
MAX ( T3Dates[Date] )
RETURN
CALCULATE (
LASTNONBLANKVALUE ( T3[Date], SUM ( T3[Amount] ) ),
REMOVEFILTERS ( T3Dates ),
T3Dates[Date] <= maxdate
)
Pat
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
56 | |
27 | |
23 | |
15 | |
11 |
User | Count |
---|---|
78 | |
63 | |
47 | |
17 | |
12 |