Create a measure to include latest data across category rather than within category in a Treemap

[chamomile16]

I have the below data:

ReportingDate	ValuationType	ClosingValuation
3/30/2023	Recent acquisition cost	100
3/30/2023	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	External Manager	50
6/30/2020	Independent Valuation	50
6/30/2020	NULL	56
6/30/2020	Desktop Review	606
6/30/2020	Internal Valuation	60

I created a measure with the DAX code like below. I want to show the sum of latest valuation (valuation with latest reporting date) by default (not applying any filters).

Total Latest Valuation = 
  
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  
    )  
  

RETURN  
    CALCULATE (  
        SUM('I&RR Valuation'[Closing Valuation]),  
        'I&RR Valuation'[Reporting Date] = EffectiveDate
    )  

The code works fine for my other visuals, but when I use it as the Values of my treemap, and use ValuationType as Category, it isn't working as expected:

Here, Desktop Review should not be included, as the latest reporting date for records with this Valuation Type is 2020-06-30. 2020-06-30 is not the latest reporting date in the table.

What I want is only records with reporting date = 2023-03-30 as this is the latest reporting date in the table. Expected output:

I can get this expected output when selecting 2023-03-30 in the ReportingDate filter. It doesn't show this expected result when no filters are applied.

Looks like the issue is that the treemap determines the latest valuation within each category rather than across categories, so it includes the sum of latest valuation per valuation type.

Is there a way to have the treemap show the expected result, by modifying DAX code, ignoring category when determing latest valuation or any other methods?

I hope my description of the problem makes sense. Thanks very much for any advices/hints!

[chamomile16]

Note: I forgot to mention in the original post that I enabled personalized visualization for my report.

Therefore, only modifying DAX to include ValuationType may not be the best solution, as users may select other columns as Category when personalizing the visual. 

This is also the reason why this problem gives me so much headache......really need some pro's help please! Thank you!!

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[chamomile16]

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.

[chamomile16]

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.