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