cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Super User

## Calculate Average by Day of Week

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,

1 ACCEPTED SOLUTION
Super User

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,

3 REPLIES 3
Super User

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

Super User

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.

Super User

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,