Forum Discussion
Dynamic calculation in DAX using variable / lookup table?
I am wondering if it is possible to create a dynamic calculation in DAX. The denominator that I am using in the calculation may change based on a date and another attribute...
What I am looking to achieve is as follows:
The monthly capacity % used per site needs to be calculated. At present this is done by summing: the Units per site to get a monthly total of units and dividing this by a total capacity per site. Total capacity is calculated by multiplying the number of days in a month by a daily capacity for a site. The daily capacity can vary from month to month for a site and is not the same for all sites, the daily capacity is not currently stored or part of the data set. The data set is similar to the below:
| Month | Client | Site | Units |
| 1/01/2017 | a | AA | 8 |
| 1/01/2017 | b | AA | 7 |
| 1/01/2017 | c | AA | 8 |
| 1/02/2017 | a | AA | 5 |
| 1/02/2017 | b | AA | 7 |
| 1/02/2017 | c | AA | 10 |
| 1/01/2017 | d | BB | 6 |
| 1/01/2017 | e | BB | 5 |
| 1/01/2017 | f | BB | 11 |
| 1/02/2017 | d | BB | 6 |
| 1/02/2017 | e | BB | 9 |
| 1/02/2017 | f | BB | 11 |
An example of the calculations for Site AA for Jan-17 & Feb-17 are as follows (this would also apply for B which would have a different daily capacity):
Jan Site AA
Total of units = 23; Days in month = 31; Daily capacity = 1; Total capacity : 1*31 = 31; Jan-17 Capacity used %: 23/31 = 74.19%
Feb Site AA
Total of units = 22; Days in month = 28; Daily capacity = 1.2; Total capacity: 1.2*28=33.6; Feb-17 Capacity used %: 28/33.6 = 83.33%
Would there be way in DAX to dynamically calculate the monthly Capacity used %?
2 Replies
You should start capturing daily capcity in a seperate table. In addition, you should have a Date Dimension table for computing total days and slicing by Month. With these 2 it should be possible to achieve this easily in DAX.
- Thank you. I have added a separate table for capacity and calendar. Because the reported results are required at a monthly aggregate level only for now, I have been able to undertake the joining and aggregation using power query.
