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
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.
Anything related to PowerPivot and DAX Formuale
1 post • Page 1 of 1
Who is online
Users browsing this forum: No registered users and 8 guests