Average Help

[shayden]

I have a reqeust to graph today's volume of calls against the 6 week historic average but only where the same Day Of Week (Monday = Monday) is averaged.  I have the following measures created, but need to figure out how to update the average to select only same day of week as the current date.

Average_Offered = AVERAGEX(VALUES(CRM_Interval_Day_V21[CallDate]),[Total_Offered])

Current_Offered = CALCULATE(SUM(CRM_Interval_Day_V21[InboundOffered]),FILTER(CRM_Interval_Day_V21,CRM_Interval_Day_V21[CallDate]=TODAY()))

I also added the Day name as a field in my data prep.  I am connecting to a PowerBI Online Service file, so I unfortunately can't bring in any additional tables or create a new table in my pbix.  Any advice would be greatly appreciated!

thanks

Steve

[Anonymous]

Hi @shayden,

You can try to use weekday function to filter your table.

Average_Offered 6Week =
VAR current_Date =
    MAX ( CRM_Interval_Day_V21[CallDate] )
RETURN
    AVERAGEX (
        FILTER (
            ALLSELECTED ( CRM_Interval_Day_V21 ),
            WEEKDAY ( CRM_Interval_Day_V21[CallDate], 1 ) = WEEKDAY ( current_Date, 1 )
                && CRM_Interval_Day_V21[CallDate]
                >= current_Date - 42
                && CRM_Interval_Day_V21[CallDate] <= current_Date
        ),
        [Total_Offered]
    )

Reference:

WEEKDAY Function (DAX)

Regards,

Xiaoxin Sheng

[Ashish_Mathur · Accepted solution]

Hi,

You may refer to my solution here.

Hope this helps.

[shayden]

@Ashish_Mathur  This format worked.  I had to change some dates to reflect TODAY versus MAX('Date'), but I finally got it to work.  Thank you very much for your quick response!

Average_Offered_Weekday = AVERAGEX(FILTER(DATESBETWEEN(CRM_Interval_Day_V21[CallDate],MIN(CRM_Interval_Day_V21[CallDate])-43,TODAY()-1),WEEKDAY(CRM_Interval_Day_V21[CallDate],2)=WEEKDAY(TODAY(),2)),[Total_Offered])

[Ashish_Mathur]

You are welcome.