Forum Discussion

chris886's avatar
chris886
Frequent Visitor
3 years ago
Solved

Help with Measure - Daily Average excluding weekends

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.

Need Help
  • v-jingzhang's avatar
    v-jingzhang
    3 years ago

    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.

2 Replies

  • rsbin's avatar
    rsbin
    Community Champion
    3 years ago

    chris886 ,

    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,

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    3 years ago

    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.