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

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

  • 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.