March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hello,
Hoping this is a simple solve, I just don't have the experience to figure it out yet.
My dataset has a 'Measure X' that represents a count of events. I'd like to create a measure that can calculate the daily average events for the previous 14 days, excluding any weekend days. Bonus points if there's a way to specifically make it also exclude US holidays (but I'm fine if that's not possible). We only get events on working days, so when I try to do just a simple relative date filter, the average gets diluted by the weekends/holidays.
Could someone help me with the DAX on this?
Thank you.
Solved! Go to Solution.
Hi @chris886
It is recommended to prepare a calendar table with a WorkingDay column which has 1 for working days and 0 for weekends/holidays. Create a relationship between the original table and the calendar table on Date column. Then you can try the following measure
Avg of Measure X =
VAR _lastDate =
TODAY ()
RETURN
DIVIDE (
CALCULATE (
[Measure X],
DATESINPERIOD ( 'Calendar'[Date], _lastDate, -14, DAY )
),
CALCULATE (
COUNT ( 'Calendar'[Date] ),
DATESINPERIOD ( 'Calendar'[Date], _lastDate, -14, DAY ),
'Calendar'[Working Day] = 1
)
)
I have created a sample attached at bottom. Hope it helps.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Hi @chris886
It is recommended to prepare a calendar table with a WorkingDay column which has 1 for working days and 0 for weekends/holidays. Create a relationship between the original table and the calendar table on Date column. Then you can try the following measure
Avg of Measure X =
VAR _lastDate =
TODAY ()
RETURN
DIVIDE (
CALCULATE (
[Measure X],
DATESINPERIOD ( 'Calendar'[Date], _lastDate, -14, DAY )
),
CALCULATE (
COUNT ( 'Calendar'[Date] ),
DATESINPERIOD ( 'Calendar'[Date], _lastDate, -14, DAY ),
'Calendar'[Working Day] = 1
)
)
I have created a sample attached at bottom. Hope it helps.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
DAX has a WEEKDAY function
https://docs.microsoft.com/en-us/dax/weekday-function-dax
If you are using a Date Table, you can create a Calculated Column and determine Weekday.
If not, simply create a new column in your data table.
Then add a FILTER to your Measure and exclude Days 6 and 7 (depending on which return_type you decide to use).
Hope this helps.
Regards,
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
123 | |
85 | |
85 | |
70 | |
51 |
User | Count |
---|---|
205 | |
153 | |
97 | |
79 | |
69 |