Problems with calculating a load factor curve

Anything related to PowerPivot and DAX Formuale
Stormrat
Posts: 1
Joined: Thu May 21, 2020 10:18 pm

Problems with calculating a load factor curve

Postby Stormrat » Thu May 21, 2020 10:28 pm

I am still a newbie with dax and I am trying to solve the following problem since a few days.

I want to calculate a load factor development curve for a performance. I have the following data:

- Performance Date
- Booking Date
- Lead days to Performance
- cumulative ticket count
- Capacity

If I divide the ticket count by the capacity I get the load factor. Then I add the days to get a development.

Performance day Lead days ticket count capacity load factor
01.01.2021 100 5 100 5%
01.01.2021 99 10 100 10%
01.01.2021 95 20 100 20%

If I just take one specific date everything works fine. But I get a problem if I change the performance day into a performance month. Let´s say there are 5 performances in January 2021. Each performance has a capacity of 100. That´s the result:

Performance month Lead days ticket count total capacity load factor Performance count
Jan 2021 100 50 500 10% 5
Jan 2021 99 55 100 55% 1
Jan 2021 95 75 300 25% 3

If a performance doesn´t get a booking on a specific lead day the capacity of the performance is not included in the calculation. For example, just one performance got a booking 99 days before the performance. Because of this I don´t have any data for the other 4 performances in this row.

Is there a way to get the maximum of the capacity into the table? I tried it with several max functions but none worked.

Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 8 guests