Calculate Average by Day of Week

[rsbin]

Hello All,

I have the requirement to calculate Average Volume by Day of Week.  I am using a bullet chart as follows:

PD represents Prior Day.  As I am writing this on Monday, PD represents volume for Sunday. DAX Formula I use is:

LiftWorkOrders_PD = CALCULATE( [LiftWorkOrders],
                             FILTER(DIM_Date_Slicer, DIM_Date_Slicer[Date] = TODAY() - 1 ))

My User Group would like to compare this value to the following:

1) Average LiftWorkOrders for the previous 4 Sundays

2) Average LiftWorkOrders for the previous 13 Sundays

3) Average LiftWorkOrders for all Sundays year to date.

On Tuesday, the chart would represent values for Mondays, etc.  I am kinda stuck on the best way to approach this.  Would very much appreciate assistance to get me pointed in the right direction.

As always, thanks in advance and best regards,

[amitchandak]

@rsbin , Example for lat 4 sunday

LiftWorkOrders_PD = CALCULATE( [LiftWorkOrders],
FILTER(DIM_Date_Slicer, DIM_Date_Slicer[Date] >= (today() - weekday(TODAY(),2) - 4*7)  && DIM_Date_Slicer[Date] <= TODAY()  && weekday(DIM_Date_Slicer[Date],1) =1 ))

[rsbin]

@amitchandak 

Thank you very much for the prompt response.  If I understand the Filter correctly, it appears the "1"

(or Sunday) is hardcoded into the Measure.  Ideally, I would need this to be a variable so that it changes as we progress through the week.  I guess I could use a SWITCH statement and copy this 7 times, but figure there should be a way to create this as a variable.  I was thinking something along the lines of a virtual table? Again, really appreciate the quick response.

[rsbin · Accepted solution]

@amitchandak 

I was able to convert one of my Moving Average virtual tables into what I think works for this.

LiftWorkOrders_4DayAvg = 
VAR _LastDate = TODAY() - 8
VAR _Weekday = WEEKDAY( _LastDate )
VAR _Duration = 3           // Prior 3 Weekdays + LastDate gives 4 Periods
VAR _FirstDate = _LastDate - _Duration*7
VAR _CalculationPeriod = // Isolate the 4 Prior Weekdays
                                FILTER( ALL ( DIM_Date_Slicer ),
                                  AND( AND (DIM_Date_Slicer[Date] >= _FirstDate,
                                       DIM_Date_Slicer[Date] <= _LastDate ),
                                       WEEKDAY(DIM_Date_Slicer[Date]) = _Weekday ))

VAR _MovingAverage = CALCULATE (AVERAGEX( DIM_Date_Slicer,[LiftWorkOrders]), _CalculationPeriod )

RETURN
       _MovingAverage

Not sure if this is the cleanest way to accomplish what I need, but the few tests I have done seem to indicate it works.

Now I should be able to replicate for any period the Users require.

Want to give thanks and kudos for giving me some hints, esp. with respect to the Weekday Function.

Thanks again and regards,