Forum Discussion

PANNEER's avatar
PANNEER
Frequent Visitor
9 months ago
Solved

occupancy & churn rate

Hi Friends,
Currently I'm working on Agreements Dashboard in power bi.
what is the churn rate and occupancy rate dax formula for agreements dashboard?
can anyone provide me the solution for this?

Need Help

10 Replies

  • rohit1991's avatar
    rohit1991
    Super User
    9 months ago

    Hii PANNEER 

     

    Churn rate and occupancy rate depend on how your agreements table is structured, but the standard formulas in Power BI are:

    1. Occupancy Rate
      Percentage of active agreements vs total capacity.
    Occupancy Rate =
DIVIDE(
    CALCULATE(COUNTROWS(Agreements), Agreements[Status] = "Active"),
    CALCULATE(COUNTROWS(Agreements))
)
    1. Churn Rate
      Percentage of agreements that ended during the selected period.
    Churn Rate =
DIVIDE(
    CALCULATE(COUNTROWS(Agreements), Agreements[EndDate] <= MAX('Date'[Date])),
    CALCULATE(COUNTROWS(Agreements), ALL(Agreements))
)

    These are the most commonly used KPI definitions for agreement dashboards.

  • mh2587's avatar
    mh2587
    Super User
    9 months ago

     

    Generic Formula:

    • Churn Rate: The percentage of agreements that ended within the selected period.

    • Occupancy Rate: The percentage of units or rooms that are currently occupied by active agreements.

     

  • PANNEER's avatar
    PANNEER
    Frequent Visitor
    9 months ago

    Hi,
    Thanks for the providing the solution, Clarify the below doubt.
    Is the occupancy rate dax formula= Active agreements/agreements or Active agreements/Total units ?
    and
    In my agreements table i have start date and end date
    Do i need to create a separate date table with start date. please advice?

      • v-menakakota's avatar
        v-menakakota
        Community Support
        8 months ago

        Hi PANNEER ,
        Thanks for reaching out to the Microsoft fabric community forum. 

         

        I would also take a moment to thank amitchandak   , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

        I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you .

         

         

        Best Regards, 
        Community Support Team  

    • MattiaFratello's avatar
      MattiaFratello
      Super User
      9 months ago
      1. Active agreements/Total units
      2. It is recommended to create a date table if you want to use DAX time intelligence functions, such as calculating occupancy across time ranges or slicing by reporting periods. The date table should be continuous, cover all relevant dates, and have a single column with unique date values. Then relate the agreements' start and end dates to this date table, often using inactive relationships to allow custom measures for periods covered by each agreement. 

         

        • Create a date table using Power BI's CALENDAR or CALENDARAUTO DAX functions.

        • Mark your date table as a "Date Table" in Power BI for enabling advanced time-based calculations.

        • Set up relationships from your agreements' start/end dates to the date table (these can be inactive, used with DAX logic).

       

      If this helped please feel free to give kudos 👍