Sum results by filtering out duplicate references keeping latest entry

[julesdude]

I've the following table in my data model:

Leased Unit

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

I want to sum the Leased Area for each Unit Reference for each asset. My trouble is how to handle the duplicate Unit References. Some references appear more than once against the asset they relate to, so I would only like to sum the Leased Area where the Commencement Date is the latest relative to my [as of date] measure (a measure returning date from a date picker in my report).

In the above example, with the [As Of Date] measure being 31/12/2022, Asset 100 would have a total Leased Area of 62840 as there are duplicates of unit references CCC and DDD so the latest Commencement Date Leased Area values are taken.

How can this be done in DAX?

[wdx223_Daniel · Accepted solution]

NewMeasure=CALCULATE(SUMX(VALUES(Table2[Unit Reference]),LASTNONBLANKVALUE('Table2'[Commencement Date],SUM('Table2'[Leased Area]))),'Table2'[Commencement Date]<=date(2021,12,31))

[julesdude]

Hi Daniel @wdx223_Daniel 

Thanks for this. It works. I am trying to now fuse your solution into an existing measure I have which filters out a few other conditions.

I've started a new thread here for help doing that as I am not sure I am adapting correctly.

[ribisht17]

@julesdude 

How it is adding up to 62840 ?

As per your need I guess you need to make use of similar DAX function to get the latest date for any asset/unit ref combination, it will help you to equate the same with your date and exclude the non-latest one

Max Comm Date = CALCULATE(max(Asset[Commencement Date]),ALLEXCEPT(Asset,Asset[Asset   ],Asset[Unit Reference  ]))

Regards,

Ritesh

Mark my post as a solution if it helped you| Munde and Kudis (Ladies and Gentlemen) I like your Kudos!! !!
My YT Channel Dancing With Data !! Connect on Linkedin !! PL 300 Certification Series

[julesdude]

Hi @ribisht17 
It sums to 62840 because there are two duplicate unit references (AAA and DDD). In these cases the Leased Area amount with the latest Commencement Date should only be taken.

Leased Unit

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