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.
there is a mistake in the previous filter && 'Calendar'[Date]<='Order Date'[Date Key] should have been
&& 'Calendar'[Date]<=MAX('Order Date'[Date Key])
If you use the TODAY function and use a filter to exclude the current month then only the workdays from the previous full months will be included in resulting count.
I really appreciate your patience in working through this with me.
Using && 'Calendar'[Date]<='Order Date'[Date Key] defines the Calendar date range based on the order date so that order date is still subject to a lack of data in its join to the fact table.
Consider the following simplified illustration. Assume the slicer on the Order_Date_Dim has January and February calendar months selected and today is Feb 3. We would want our denominator measure to include the 3 work days in February in addition to the January work days. But because no Feb data exists in the fact table yet, only the Jan data rows from the Order Date Dim will be returned. Therefore when you perform "'Calendar'[Date]<=MAX('Order Date'[Date Key])" it will not pick up our Feb days because the MAX in the returned data set is 1/31. Make sense?
So using this formula WorkDaysInPeriodUntilToday = CALCULATE(COUNT('Calendar'[date]),FILTER('Calendar','Calendar'[work_day_flag]="Yes" && 'Calendar'[date]<=TODAY()))
should get what you need. This screenshot shows the monthly revenue per workday and an overall total for the months selected in the slicer.
and if only January was selected in the slicer you would get this
There are also measures that you can define that will calculate to the totals that are shown in the Matrix using the ALLSELECTED function.
Total Workdays2 = CALCULATE(COUNT('Calendar'[date]),FILTER(ALLSELECTED('Calendar'),'Calendar'[work_day_flag]="Yes" && 'Calendar'[date]<=TODAY())) --33
use a similar ALLSELECTED function on the revenue calculation Revenue2=CALCULATE(SUM(Order_Fact_Table[Amount]),ALLSELECTED('Calendar')) --36,085.42
Revenue Per Day2 = Divide(Revenue2,Total Workdays2) --1093.50
Do you have an actual Calendar table that has the workday flag that is related to the Orders table, or did you merge them?
With an actual Calendar table the formula would be
Days In Period=CALCULATE(COUNT('Calendar'[Date]).
FILTER('Calendar'[Work Day Flag]=True()
&& 'Calendar'[Date]<='Order Date'[Date Key])) -- Cound also use the TODAY() function here.
Where you are counting the workdays from the Calendar table not your orders table therefore it will not matter if there were no orders on some day(s).
Using && 'Calendar'[Date]<='Order Date'[Date Key] defines the Calendar date range based on the order date so that order date is still subject to a lack of data in its join to the fact table. Consider the following simplified illustration. If the slicer on the Order_Date_Dim includes Jan and Feb, it will only return the Jan data data because no Feb data exists in the fact table yet.
Using && 'Calendar'[Date]<=TODAY() function would overcome this, however the user may not want to include the current time period (i.e. month) in the slicer selection
The trick to solve this problem is to remove the relationship. You can read my article here
This article is similar, not identical, but I think it will help you solve the problem. Jump to the bottom where I show the 2 tables without a relationship.
If you need the relationship for other purposes, then make it inactive and then call it using USERELATIONSHIP in your other measures.
Thank you so much for your quick response, Matt. Your article is excellent and I really like the approach you take, however I think this would be vulnerable to error when applied to my problem and here's why: My report uses a Fiscal Month (in the form of YYYY-MM) slicer from an attribute of my date dimension. That date dim also includes columns for FiscalMonthBeginDate and FiscalMonthEndDate that I could key off of to provide the logic:
Days In Period = CALCULATE( COUNT('Order Date'[DateKey]), FILTER( 'Order Date','Order Date'[Date] >= MIN(WorkDaysTable[FiscalMonthBeginDate]) && 'Order Date','Order Date'[Date] <= MAX(WorkDaysTable[FiscalMonthEndDate]) ) )
So far so good. I would need to further refine this to account for accurate calculations in the midst of the current fiscal month as thus:
Days In Period = CALCULATE( COUNT('Order Date'[DateKey]), FILTER( 'Order Date','Order Date'[Date] >= MIN(WorkDaysTable[FiscalMonthBeginDate]) && 'Order Date','Order Date'[Date] <= MAX(WorkDaysTable[FiscalMonthEndDate]) && 'Order Date','Order Date'[Date] < TODAY() ) --would need to modify to return date only ) )
This should work under most scenarios, but unfortunately this is still dependent on the presence of fact records in each Fiscal Month selected in the slicer. There are scenarios whereby other slicers may greatly reduce the number of fact records being referenced, likewise in the early days of a new fiscal month there may not yet be transactions that meet the slicer criteria. In these cases the min or max will not include the intended time period for the count of work days.
It would be nice if in a DAX expression there were a means to reference the actual selections made in a slicer. This can be done in tools such as Business Objects. Is there a way to do this in Power BI?
Any other ideas?
Sorry, I don't understand your explanation of why it won't work. If you remove the relationship and use filter to filter the fact table between the first date in the filter context and the
last date in the filter context, why won't it work.
if the slicer selects Jan through March and there are no records for Feb, why is this a problem? As I understand your requirement, the fact that there are no records in Feb is irrelevant.
Of course my understanding of the problem may be flawed.
Let's say that in the period slicer, Jan and Feb are selected and today is Feb 3 of 2017. Now let's say that there are not yet facts in Feb that meet all the slicers' criteria. We still need to include Feb 1 & 2 as work days in the calculation denominator, but these dates will fall off because the MAX(WorkDaysTable[FiscalMonthEndDate]) for the available data will be January 31.
I hope I've explained this condition adequately.