Forum Discussion

shayden's avatar
shayden
Frequent Visitor
8 years ago
Solved

Average Help

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

Need Help

4 Replies

    • shayden's avatar
      shayden
      Frequent Visitor
      8 years ago

      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])

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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