Number of Active Units in all Apartments in Region Compared to Sell Date and Purchase Date

[PhilLockWP]

Hello,

I have an Entity Property List (attached) of apartments with columns for Region, Property Name, Units in the Apartment, Purchase Date, and Sale date for those that have been sold.

I want to take my cash flow that spans the last three years and divide that by the units so that I can get Revenue/unit for a particular region or property. I want the WEST region's units to be lower in 2024 than they were in 2023 because 8 properties were sold in Dec 2023. I am having trouble getting the units to dynamically change with time.

Date Table:

Entity sheet:

Relationships: 

 I looked up similar questions on this forum, and copied a measure from this one: Solved: Count of Items by date - Microsoft Fabric Community

I have this measure: 

Active by Sales Date Entity = CALCULATE(COUNT('Entity Simpler'[Property]), FILTER(ALLSELECTED('Date'),[Date].[Date] < MAX('Entity Simpler'[Sale Date])))

I want it to show 18 properites in the West before Dec 7, 2023 and 14 after Dec 20, 2023, but it is flipped, and I am not sure how to fix it.

My ultimate goal is to show units changing over time as properties are purchased and sold so that I can get an accurate Revenue per Unit measure on my report.

Here is my file: https://drive.google.com/drive/folders/1ia1NuBXeoKrKyPfpOIQVQtLqTggdyWVe?usp=sharing 

Thank you so much

[Ashish_Mathur]

Hi,

Show the expected result in a Table format.

[PhilLockWP]

Yes, shown below in my reply to v-cgao-msft

[v-cgao-msft]

Hi @PhilLockWP ,

Relationship:

And please create a new measure:

Active by Sales Date Entity = 
VAR __cur_date = SELECTEDVALUE('Date'[Date])
VAR __count_all = CALCULATE(COUNT('Entity Simpler'[Property]),'Entity Simpler'[Purch Date]<=__cur_date)
VAR __count_sale = CALCULATE(COUNT('Entity Simpler'[Property]),'Entity Simpler'[Sale Date]<__cur_date && 'Entity Simpler'[Sale Date]<>BLANK())
RETURN
__count_all - __count_sale

Output:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[PhilLockWP]

Hi @v-cgao-msft 

Thank you so much for your response! I realize now I gave bad instructions, but this is exactly what I asked for.

I thought I could multiply the count of active properties by the property units, but Power Bi does not know which units are active to multiply by the properties. In my test, I multipled your measure by Calculate(MAX('entity simpler'[units])), but this gave the wrong result, because it took the max of all the unit counts, not the active property unit counts.

@Ashish_Mathur my desired output in a table format for January 2024 would be as follows: I want to see the active properties WEST1-WEST14 in the first column with their associated unit counts in the second column which should sum to 1,527.

I should then be able to take my financial data (not shown in the file), slice it on the "WEST" region, and divide it by the active property unit counts to get Revenue per Unit in the WEST region or for a particular property in the region.

So if I have $100,000 of revenue in WEST in January 2024, I can get a $100,000/1,527 active property units = $65.5 Revenue per unit.

If I have $100,000 of revenue in West in November 2023 (when there were 22 active properties and 2,363 units), I can get $100,000/2,363 active property units = $42.3 Revenue per unit.

Ultimately, I want to be able to graph this series over time of Revenue per unit.

[Ashish_Mathur · Accepted solution]

PBI file attached.  December 2023 is 1527

[Ashish_Mathur]

PBI file attached.  December 2023 is 1527