Forum Discussion

MJoseph's avatar
MJoseph
Regular Visitor
2 years ago

Power BI historical data

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 ‘Oc...
Need Help
Show and Tell
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
MJoseph
Regular Visitor
2 years ago

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's avatar
    MJoseph
    Regular Visitor
    2 years ago

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