Measure for KPI's based on latest status

[HFBackelor]

Hello.

I am currently working on a report where i want to analyze a large dataset of commercial leases.

The target fuctionality is to have a report with a time-based slicer that can show both data from individual leases at any given point, and aggregated KPI's such as occupancy ratios, and how they've changed over time.

I've managed to create measures for producing the latest values/status for individual units, but those don't seem to work at an aggregated level, especially when trying to introduce filters based on latest status etc.

The dataset structure is similar to the one I'm posting below.

I really appreciate any help and/or pointers.

Unit ID	Lease ID	Latest Update	Status	Area
1	1-1	2021-12-31	Let	24
1	1-2	2022-03-31	Let	24
1		2022-06-30	Vacant	24
1	1-3	2022-08-01	Let	24
1		2023-06-01	Vacant	24
1	1-4	2023-07-01	Let	24
2	2-1	2019-01-01	Let	50
2	2-2	2023-09-01	Let	50
2		2023-11-01	Vacant	50
3		2016-11-30	Vacant	70
3	3-1	2017-01-01	Let	70
3	3-2	2018-03-01	Let	70
3	3-3	2021-08-01	Let	70
3	3-4	2022-04-01	Let	70
3		2023-01-01	Vacant	70
3	3-5	2023-05-25	Let	70

-*Update*-

Adding visualization of desired result, and description of the measure i've used so far to calculate latest value on unit level, and the measure i've been trying to combine it with to calculate aggregated KPI's for occupancy:

Below example would be the result of a slicer set at 2023-06-30

Unit ID	Lease ID	Latest Update	Status	Area
1		01/06/2023	Vacant	24
2	2-1	01/01/2019	Let	50
3	3-5	25/05/2023	Let	70

Aggregated occupancy rate would amount to 83%

Current Measure for latest value for area: 

Latest Area =

var LastArea = calculate(sum(Data[Area]),Data[Latest Update]=max(Data[Latest Update]))

Return sumx(values(Unit ID]),LastArea)

Current attempt at measure for aggregated occupancy that does not work:

Occupied Area =

var RelativeDate = calculate(max(Data[Latest Update]),Data[Latest Update]<=max('Calendar'[Date]))

var LatestUnitID = calculate(max(data[Unit ID]),Data[Status Update]=max(Data[Status Update]))

var DateTable1 = summarize(Data,Data[Unit ID],"RelativeStatusChange", RelativeDate)

var LatestStatusUpdate = maxx(DateTable1,[RelativeStatusChange])

var LatestStatus = lookupvalue(Data[Status],Data[Latest Update],LatestStatusUpdate,Data[Unit ID],LatestUnitID)

Return calculate(sumx(values(Data[Unit ID]),[Latest Area]),LatestStatus="Let")

[v-yilong-msft · Accepted solution]

Hi, HFBackelor

Based on the information you have provided, Here are my answers to your questions.

1. Make a date slicer.

date =

    CALENDAR(DATE(2016,1,12),DATE(2023,08,31)).

1. Import the form as per your requirement.

1. Create a measure for checking the nearest and farthest dates.

measure =

VAR _maxdate =

    MAXX ( ALLSELECTED ( 'date' ), 'date'[date] )

VAR _minxdate =

    MINX ( ALLSELECTED ( 'date' ), 'date'[date] )

RETURN

    CALCULATE (

        MAX ( 'Table (2)'[Latest Update] ),

        FILTER (

            ALL ( 'Table (2)' ),

            'Table (2)'[Latest Update] >= _minxdate

                && 'Table (2)'[Latest Update] <= _maxdate

                && 'Table (2)'[Unit ID] = SELECTEDVALUE ( 'Table (2)'[Unit ID] )

        )

    )

1. Create a result and get its status based on the nearest date. Then you will get the latest status.

result =

    IF([measure] =

SELECTEDVALUE('Table (2)'[Latest Update],MAX('Table (2)'[Status],BLANK())

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

Clearly show the expected result in a Table format.