Skip to main content
cancel
Showing results for 
Search instead 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

Reply
rsbin
Super User
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:

rsbin_0-1630941644460.png

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

 

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

 
 

View solution in original post

3 REPLIES 3
amitchandak
Super User
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 ))

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

 

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

 
 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors