Power BI historical data

[MJoseph]

Hi

I’m new to Power BI and need help with this.

I have a Unit table where hmy is primary key, scode is the unit#, hproperty is the property the unit belongs to, and each unit has status like ‘Occupied No Notice’, ‘Notice Unrented’, ‘Vacant’ etc.. (One status at a time)

The unit has a different history table called unit_status. For example below is the history of a unit scode = 021 which is hunit = 5283.

I do have different measures like below.

Occupied Units = CALCULATE(COUNT('View - Unit'[sStatus]),FILTER('View - Unit','View - Unit'[sStatus] = "Occupied No Notice"))

Notice Rented = CALCULATE(COUNT('View - Unit'[sStatus]),FILTER('View - Unit','View - Unit'[sStatus] ="Notice Rented"))

The above measures only give values as of today. I want a visual representation of these measures historically. Means how many occupied units were there in Jan, Feb, March etc.. Something like this.

My current relation between two tables is below.

Any help will be appreciated. 

Thanks,

Maria

[v-xinruzhu-msft]

Hi @MJoseph 

You can try the following measure.

Occupied Units =
CALCULATE (
    COUNT ( 'View - Unit'[sStatus] ),
    'View - Unit'[sStatus] = "Occupied No Notice",
    CROSSFILTER ( 'View - Unit'[hmy], 'View - UnitStatus'[hunit], BOTH )
)

And you have create the date table, you can select a chart visual, and put the data field to the x-axis, then put the measure to the y-axis.

Best Regards!

Yolo Zhu

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

[MJoseph]

For the additional info, you have requested, please see below.  

Hi  @MJoseph  I have some questions, can you provide some information?

1.I found that you have multiple Occupied No Notice statuses in your unit table and multiple Occupied No Notice statuses in your status table, and you want to calculate Occupied No Notice in both tables Can you also show which column in your status table is associated with the date column in your date table below?

The unit table shows the individual units (scode) in each property(hproperty) and most of the units are occupied by tenants. That's why you see multiple occupied units. The unit table only shows the current status of the unit. On the other hand, unit_status table will have the history of what happened to each unit. For example, a tenant was occupied in the unit 5283 from 2013-12-01 to 2020-11-30 (lease from and Lease to fields). But the status changed on 2015-01-01 (dateOccurred).  Then the tenant were put on notice on 2020-09-17. And couple of other status happended after. Then other tenant moved into the same unit on 2021-03-09. So basically I wanted to know how many units were occupied by tenant in each month from the unit_status table. The date table is connected to 'dtDateoccured in unit_status' table. 

2.Based on the ' history of a unit scode = 021 which is hunit = 5283.' I am confused that in the unit table based on the picture you have offeded , when the scode is 21, the hmy is 2269 instrad of 5283, I cannot understand it.   Best Regards! Yolo Zhu  

Sorry for the confusion. That picture was not specific to the 5283 unit. But to give an idea about the unit table structure.  This is the data for unit = 021 which is 5283 for the property 410b1 which is hproperty 15. This means, you need to look at the combination of hproperty and scode. 

Hope it is clear. 

Thank you so much for your time. 

[MJoseph]

Thank you for the response. But looks like it is not bringing the actual numbers. 

I have added the measure and date as per below. 

Its only bringing 2 units for Feb and 1 for March for one property (I'm testing it for one proeprty for 2024). But the historical unit status for Occupied for the selected proeprty is like this. 

Do you think we need to have a grouping done in the unit_status table to make this work? 

Thanks,

Maria

[v-xinruzhu-msft]

Hi @MJoseph 

You can try the following measure.

Occupied Units = CALCULATE(COUNT('View - UnitStatus'[sStatus]),FILTER('View - Unit','View - Unit'[sStatus] = "Occupied No Notice"))

Best Regards!

Yolo Zhu

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

[MJoseph]

Hi Yolo, 

I have used this below measure. 

Occupied Units Hist =

CALCULATE (

    COUNT ('View - UnitStatus'[sStatus]), FILTER('View - UnitStatus', 'View - UnitStatus'[sStatus] = "Occupied No Notice"))

But it only provides how many new occupancy happened in each month. For example, let's say we have 50 people occupied by Nov in a property 410b1, 2 moved out at the end of Nov, and One moved in 1st of Dec, the count for Dec should be 49. So it should consider the number of units so far it is occupied by month. 

I can explain this in terms of SQL. You have to first group the unit_status table with hmy, sDateoccured both in desc and then take the top 1 entry. Count the entry if the unit_Status [status] = Occupied No Notice. 

Thanks,

Maria

[MJoseph]

Hi, any suggestions will be appreciated as I cannot figure this out at my end as I am fairly new to Power BI DAX.