cancel
Showing results for
Did you mean:
Regular Visitor

## 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 2
Established Member

## Re: Dynamic calculation in DAX using variable / lookup table?

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.

Highlighted
Regular Visitor

## Re: Dynamic calculation in DAX using variable / lookup table?

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.