Forum Discussion
Need help summing only latest value for a reference when calculating SUM result
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 )
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.
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.
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/