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:
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 %?
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.