Need help summing only latest value for a reference when calculating SUM result

[julesdude]

I am piecing together a solution provided here with my own.

Here is part of my table in my data model, there are many Asset References but here are the rows corresponding to one of them:

Asset Reference	Unit Reference	Leased Area	Status	Commencement Date	Expiration Date	Termination Date
100	AAA	14654	Active	20-Jun-17	19-Jun-27
100	BBB	16387	Active	11-Aug-14	11-Feb-33
100	CCC	6597	Active	28-Jan-18	27-Jan-28
100	DDD	4747	Active	30-Apr-21	29-Apr-31
100	EEE	12366	Active	16-Aug-13	15-Aug-23
100	CCC	6597	Holding Over	28-Jan-08	27-Jan-18
100	FFF	2912	Holding Over	30-Jan-18	29-Jan-28
100	GGG	5177	Holding Over	06-Feb-18	05-Feb-28
100	DDD	4747	Terminated	15-Jul-14	14-Jul-24	29-Apr-21
100	DDD		Terminated	21-Nov-20	29-Jan-21	22-Nov-20
100	FFF		Active	27-Jan-23	26-Jan-33

My matrix visual tries to sum this table by Leased Area total for each Asset Reference - so one row for each Asset Reference

This particular Asset Reference is not working well with my DAX measure.

The rules in the measure are listed below. [as of date]  is the date a user has chosen in my report:

1. Filter keeping results where Commencement Date is <= [as of date] and Expiration Date >= [as of date]
OR
either Lease Status = "Holding Over" or Lease Status = "Month-to-Month"

2. Filter further keeping rows where Termination Date = blank or >= [as of date] if there is a date there

3. Filter further keeping the Leased Area value with the latest Commencement Date IF there is a duplicate Unit Reference. So in the case of Unit Reference CCC with the [as of date] being 31/12/2022 you'd want to keep the row with the Commencement Date of 28-Jan-18 and discard the earlier dated one. 

4. SUM the above filtered results to give a total Leased Area for this particular Asset Reference

My DAX is succesful with the first two items, but I'm not sure it is correctly applying the 3rd because for the above I am getting a total Leased Area of 69.437 but it should be 62,840.
My DAX:

Total Leased Area (Current) = 
VAR asOfDate = [As Of Date]

VAR filtTblOne =
    FILTER (
        Lease_Unit,
        AND(Lease_Unit[Expiration Date] >= asOfDate, Lease_Unit[Commencement Date] <= asOfDate)
            || ( Lease_Unit[Lease Status] = "Holding Over"
            || Lease_Unit[Lease Status] = "Month-to-Month" )
    )
VAR filtTblTwo =
    FILTER (
        filtTblOne,
        Lease_Unit[Termination Date] = BLANK ()
            || Lease_Unit[Termination Date] >= asOfDate
    )

VAR filtTblThree = 
    FILTER (
        filtTblTwo,         
        Lease_Unit[Commencement Date] <= asOfDate
        && LASTNONBLANKVALUE (
            Lease_Unit[Unit Reference],max(Lease_Unit[Commencement Date])
        )
    )

RETURN


    CALCULATE ( SUM ( Lease_Unit_DST[Leased Area] ), filtTblThree )

Any suggestions on where I'm going wrong?

[wdx223_Daniel]

Total Leased Area (Current)=VAR asOfDate =
DATE ( 2021, 12, 31 )
RETURN
CALCULATE (
SUMX (
VALUES ( 'Lease_Unit'[Unit Reference] ),
LASTNONBLANKVALUE (
'Lease_Unit'[Commencement Date],
SUM ( Lease_Unit[Leased Area] )
)
),
AND (
Lease_Unit[Expiration Date] >= asOfDate,
Lease_Unit[Commencement Date] <= asOfDate
)
|| Lease_Unit[Status] IN { "Holding Over", "Month-to-Month" },
COALESCE ( Lease_Unit[Termination Date], TODAY () ) >= asOfDate
)

[julesdude]

Hi @wdx223_Daniel 

Thank you so much for coming back!

I was just checking though but it is not working for the date against the below asset's data. Based on the rules, With an as of date of 31/12/2022 I would expect a total Leased Area for this asset to be 27,309 but it is returning a 45,476 and I can't figure out why:

Asset Reference	Unit Reference	Leased Area	Status	Commencement Date	Expiration Date	Termination Date
A100	030	9142	Active	20-Feb-18	19-Feb-33
A100	020	9142	Active	26-Feb-18	25-Feb-28
A100	010	9025	Active	20-Feb-18	19-Feb-33

[wdx223_Daniel]

because in the grand total, there are two values at 20-Feb-18, and lastnonblankvalue did not get the filters comes from unit reference, so, 9142 and 9025 was calculated twice.

please try this code

=
VAR asOfDate =
    DATE ( 2021, 12, 31 )
RETURN
    CALCULATE (
        SUMX (
            VALUES ( 'Lease_Unit'[Unit Reference] ),
            LASTNONBLANKVALUE (
                'Lease_Unit'[Commencement Date],
                CALCULATE ( SUM ( Lease_Unit[Leased Area] ) )
            )
        ),
        AND (
            Lease_Unit[Expiration Date] >= asOfDate,
            Lease_Unit[Commencement Date] <= asOfDate
        )
            || Lease_Unit[Status] IN { "Holding Over", "Month-to-Month" },
        COALESCE ( Lease_Unit[Termination Date], TODAY () ) >= asOfDate
    )

[julesdude]

@wdx223_Daniel 

This is beyond brilliant, so thank you.

Of the 20 assets I've tested this against, I am now getting the correct Leased Area for all but one.

Let me just unpick that a little to see where the issue lies (looks like just one unit reference) and I'll post back in a few hours. 

Thanks again for the skilled approach.

[julesdude]

@wdx223_Daniel 

Hi Daniel, there was just one case I found so far where the solution doesn't quite handle well.

Sample dataset:

Asset Reference	Unit Reference	Leased Area	Status	Commencement Date	Expiration Date	Termination Date
A200	BR470		Active	11-Oct-21	10-Oct-22
A200	BR470		Active	05-Jan-21	10-Oct-21
A200	BR470		Active	11-Nov-20	24-Dec-20
A200	BR470		Active	02-Jul-22	01-Jul-30	03-Jul-22
A200	BR880	51400	Active	28-Jan-21	27-Jan-36
A200	BR880		Active	03-Dec-20	27-Jan-21
A200	BR520	18295	Active	04-Jan-21	03-Jan-24
A200	BR520		Terminated	03-Dec-20	02-Dec-30	03-Jan-21
A200	BR600	115869	Active	24-Jun-21	23-Jun-23
A200	BR600		Active	01-Jul-22	30-Jun-23
A200	BR600		Active	03-Dec-20	23-Jun-21

In this instance, the last Unit Reference (bottom three rows) BR600 causes the issue. It has three entries. The logic above chooses the item with a Leased Area of 115869 and sums this as part of the total Leased Area giving a total of 185,564. However, the item BR600 has other entries. If we have an [As Of Date] of 31/12/2022, this should actually qualify two items (Commencement Date 24-Jun-21 to Expiration Date 23-Jun-23 and the item in row underneath Commencement Date 01-Jul-22 to Expiration Date 30-Jun-23). It should only ever qualify one row per unit reference. In this case for BR600, as there are three rows for it and two which qualify within the Commencement and Expiration dates, the Leased Area should always be taken for the row where the Commencement Date is closest to the As Of Date AND is in the past, even if the Leased Area is blank and there is nothing to total.

So for the above, the total I should be getting is 69,695 (51400 + 18295) for this asset A200

[wdx223_Daniel · Accepted solution]

maybe you make a typo in the third row from bottom, the commencement date should be 01-JUL-21, if not, that row will not be selected. anyway, after change that date, i have made all the sample data you provided to get a correct result as the snapshoot.

=
VAR asOfDate =
    DATE ( 2021, 12, 31 )
RETURN
    CALCULATE (
        SUMX (
            VALUES ( 'Lease_Unit'[Unit Reference] ),
            SUMX (
                TOPN (
                    1,
                    FILTER (
                        'Lease_Unit',
                        'Lease_Unit'[Unit Reference] = EARLIER ( Lease_Unit[Unit Reference] )
                    ),
                    'Lease_Unit'[Expiration Date]
                ),
                'Lease_Unit'[Leased Area]
            )
        ),
        AND (
            Lease_Unit[Expiration Date] >= asOfDate,
            Lease_Unit[Commencement Date] <= asOfDate
        )
            || Lease_Unit[Status] IN { "Holding Over", "Month-to-Month" },
        COALESCE ( Lease_Unit[Termination Date], TODAY () ) >= asOfDate
    )

[julesdude]

Hi @wdx223_Daniel 

Yes there was actually no typo, just odd data from the system I'm extracting from unfortunately. Sometimes date commencement and expiration overlap with commencement and expiration dates against the same unit reference. It is just how it is.

Thanks for the amended solution. It corrected a number of additional totals I was expecting. However it is not totalling correctly for the below asset reference

Asset Reference	Unit Reference	Leased Area	Status	Commencement Date	Expiration Date	Termination Date	SUM?
C16	10	4401	Active	04-Jul-16	03-Jul-31		Y
C16	20	5309	Active	28-Nov-16	27-Nov-41		Y
C16	30	4447	Active	26-Nov-21	25-Nov-36		Y
C16	30	4447	Terminated	04-Nov-16	03-Nov-26	12-Jan-21
C16	30		Active	13-Jan-21	31-Dec-99
C16	40	1331	Active	30-Sep-20	29-Sep-30		Y
C16	40	1331	Terminated	13-Apr-17	12-Apr-32	29-Sep-20
C16	50	5595	Active	16-Dec-16	15-Dec-24		Y

 The total Leased Area for this one should be 21083 but I am getting 16636. I have created a column for the sample above to indicate the rows that qualify for summing based on the rules above.
The item that is being left out but that should be included is the item 3rd row down from the top.

[julesdude]

Hi @wdx223_Daniel 

Thankfully in the end it was a small reference adjustment to call on the order of the Commencement Date column rather than the Expiration Date in the TOPN function:

=
VAR asOfDate =
    DATE ( 2021, 12, 31 )
RETURN
    CALCULATE (
        SUMX (
            VALUES ( 'Lease_Unit'[Unit Reference] ),
            SUMX (
                TOPN (
                    1,
                    FILTER (
                        'Lease_Unit',
                        'Lease_Unit'[Unit Reference] = EARLIER ( Lease_Unit[Unit Reference] )
                    ),
                    'Lease_Unit'[Commencement Date]
                ),
                'Lease_Unit'[Leased Area]
            )
        ),
        AND (
            Lease_Unit[Expiration Date] >= asOfDate,
            Lease_Unit[Commencement Date] <= asOfDate
        )
            || Lease_Unit[Status] IN { "Holding Over", "Month-to-Month" },
        COALESCE ( Lease_Unit[Termination Date], TODAY () ) >= asOfDate
    )

I think we can tie up the thread nicely. Thanks for all your help.

Just a question on the benefit of using the COALESCE line rather than putting in its place:

        OR( 
            Lease_Unit[Termination Date] >= _asOfDate, 
        isblank(Lease_Unit[Termination Date])
        )

Your method looks cleaner, shorter. I was wondering if there were other benefits.

[wdx223_Daniel]

i think it just improves code-readability. more information about this function you can read this artical

https://www.sqlbi.com/articles/the-coalesce-function-in-dax/