occupancy & churn rate

[PANNEER]

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?

[PANNEER]

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?

[amitchandak]

@PANNEER , Active agreement is the same as what I have done for an Active employee. Files are attached after the signature, taking care of the start and end date 

[v-menakakota]

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  

[v-menakakota]

Hi @PANNEER ,

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

[PANNEER]

Hi,
 Could you please provide me the dax formula for churn rate and occupancy rate  for agreements dashboard?

[v-menakakota · Accepted solution]

Hi @PANNEER ,

Please check the pbix file which i have shared 

Thank you.

[PANNEER]

Thank you so much😊

[MattiaFratello]

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 👍

[mh2587]

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.

[rohit1991]

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))
)

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