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!
User | Count |
---|---|
90 | |
89 | |
85 | |
73 | |
49 |
User | Count |
---|---|
169 | |
144 | |
90 | |
70 | |
58 |