My date dimension has a Workday Flag Indicator which I need to use as the basis for the denominator for measures derived from the related fact table. These measures must divide dollars or quantities by the number of work days in the period, whereby that period is dynamic with respect to date-related slicer selections and the current date (i.e. AverageRevenuePerWorkday). The problem is that the date dimension is inner joined to the fact table, and if there isn't a transaction fact on a given workday in the result set, that date won't be counted as workday in the period.
So the question is how to formulate a DAX expression that will return the actual number of workdays in the given dynamic period of time from the date dimension that is irrespective to the related facts? Of course any one date-related slicer must act on both the numerator and denominator logic so I can't use a separate alias of the date dimension for thedenominator.
Here is the logic that would return the intended denominator value IF there were fact transactions for each Workday:
Days In Period = CALCULATE(
COUNT('Order Date'[DateKey]),
FILTER('Order Date','Order Date'[Work Day Flag] = TRUE())
)I'm certain there's a way to do this by applying context but my attempts have been unsuccessful so I would appreciate help from the community.
Thanks
