Forum Discussion

crisher's avatar
crisher
Icon for Helper I rankHelper I
4 years ago

Averagex with a calculate filter

I am trying to calculate the average of the Billing Total MTD, but filter out weekends or Holidays. In the below, 0 indicates that the date is neither a weekend nor a holiday. I feel like I am missing something completely basic.
 
 
MTD Daily Average = CALCULATE(
AVERAGEX (
DATESMTD ( 'Calendar'[Billing Date] ),
[Billing Total] ),
'Calendar'[WeekendOrHoliday]=0
)
Need Help

5 Replies

  • DataInsights's avatar
    DataInsights
    Icon for Super User rankSuper User
    4 years ago

    crisher,

     

    Try this measure:

     

    MTD Daily Average =
CALCULATE (
    AVERAGEX ( 'Calendar', [Billing Total] ),
    DATESMTD ( 'Calendar'[Billing Date] ),
    'Calendar'[WeekendOrHoliday] = 0
)

     

    • crisher's avatar
      crisher
      Icon for Helper I rankHelper I
      4 years ago

      Hey DataInsights, I think this is close. But, I do need the billings that are occurring over the weekend as part of Mondays' average calculation. Does that make sense?

      • crisher's avatar
        crisher
        Icon for Helper I rankHelper I
        4 years ago

        Actually, I don't think this is right. At the moment, it is simply doing the average of that day. The intent is to do a running average for the month and remove holidays and weekends. Right now, it is just doing the average of that single day. See below...

         

         

         

  • v-kkf-msft's avatar
    v-kkf-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi crisher ,

     

    If you only calculate the average for rows with data, then try the formula of DataInsights . 

     

    If you want to calculate the average for all weekdays in current month, try the following formula.

     

    Measure = 
AVERAGEX ( 
    ADDCOLUMNS (
        CALCULATETABLE (
            'Calendar', 
            FILTER (
                'Calendar',
                'Calendar'[WeekendOrHoliday] = 0 
                    && FORMAT ( 'Calendar'[Billing Date], "YYYYMM" ) = FORMAT ( TODAY ( ), "YYYYMM" ) 
            )
        ),
        "BillingTotal", COALESCE ( [Billing Total], 0 )
    ),
    [BillingTotal]
)

     

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.