cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mleepin Regular Visitor
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:

 

MonthClientSiteUnits
1/01/2017aAA8
1/01/2017bAA7
1/01/2017cAA8
1/02/2017aAA5
1/02/2017bAA7
1/02/2017cAA10
1/01/2017dBB6
1/01/2017eBB5
1/01/2017fBB11
1/02/2017dBB6
1/02/2017eBB9
1/02/2017fBB11

 

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
AkhilAshok Established Member
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
mleepin Regular Visitor
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.