Forum Discussion
Create a measure to include latest data across category rather than within category in a Treemap
Super UserHi,
PBI file attached.
Hope this helps.
Hi Ashish, I really appreciate your input!
I tweaked and applied it to my report, and it works great when it's in default state.
When I apply other filters, for example, Investment, I hope it to show the latest valuation for that investment.
E.g., when I select Apple in my filter, the expected result is to show the latest valuation for Apple (on 2020-06-30), with Valuation Type External Manager and Desktop Review.
| Reportingdate | Investment | Valuation Type | Closing Valuation |
| 3/30/2023 | ABC property | Recent acquisition cost | 100 |
| 3/30/2023 | BE property | External Manager | 229 |
| 3/30/2023 | Independent Valuation | 300 | |
| 3/30/2023 | NULL | 400 | |
| 3/30/2023 | Internal Valuation | 90 | |
| 3/30/2023 | Recent transaction price | 780 | |
| 6/30/2020 | Apple | External Manager | 50 |
| 6/30/2020 | Banana | Independent Valuation | 50 |
| 6/30/2020 | NULL | 56 | |
| 6/30/2020 | Apple | Desktop Review | 606 |
| 6/30/2020 | Internal Valuation | 60 |
But what it actually shows is Blank(), as 2020-06-30 is not the latest reporting date for whole table.
In my previous code, I used the highlighted part below to have this function work for my other visuals, but unfortunately I think this is exactly what caused my original problem in the treemap (the Desktop Review that shouldn't be there in default state)
VAR SelectedDate = SELECTEDVALUE ( 'I&RR Valuation'[Reporting Date] )
VAR EffectiveDate =
IF (
ISBLANK(SelectedDate),
IF (
HASONEVALUE('I&RR Valuation'[Reporting Date]),
VALUES('I&RR Valuation'[Reporting Date]),
CALCULATE (
MAX('I&RR Valuation'[Reporting Date]),
ALLSELECTED('I&RR Valuation')
)
),
SelectedDate
)
Is there possibly a way to get the expected result when applying other filters, so it doesn't only show the latest reporting date of the whole table? I understand that this may be hard, and thanks again for helping!
- Ashish_Mathur
Hi,
See this revised file.
Hi Ashish, thanks for the revised version. It seems to work fine for Apple, but consider if we have ABC property data as below, it will be showing both Recent acquisition cost and Internal Valuation while the desired output is Recent acquisition cost as it's the latest one for ABC property.
Reportingdate Investment Valuation Type Closing Valuation 3/30/2023 ABC property Recent acquisition cost 100 3/30/2023 BE property External Manager 229 3/30/2023 Independent Valuation 300 3/30/2023 NULL 400 3/30/2023 Internal Valuation 90 3/30/2023 Recent transaction price 780 6/30/2020 Apple External Manager 50 6/30/2020 Banana Independent Valuation 50 6/30/2020 NULL 56 6/30/2020 Apple Desktop Review 606 6/30/2020 ABC property Internal Valuation 60
- Ashish_Mathur
You are very unclear about your own data/scenarios. it jsut wasted my time to offer solutions for every scenrio that yo come up with. Think through all your scenarios very well and come up with a representative example/expected result.