cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
HelenBrunyee
Helper I
Helper I

DAX Formula for Weekly Average, to work with Date Range slicer

Hello Everyone,

 

I was hoping you'd be able to help me with getting a DAX formula together.

 

A bit of context - I have a dataset with a date table, and a table which is a list of 'Tickets issued'. They are linked via Issued Date.

I am trying to get the average number of of tickets Issued a week, for each location (tickets have the location that they were issued to).

 

I currently have the following DAX, but it isn't calculating properly (e.g. giving a greater average for a location with a lower number of total issued - see snapshot below) and I think this due to the 'Week Number' resetting at the start of each year, when my data spans 3+ years:

 

Average Issued = AVERAGEX(VALUES( 'V_Date Table'[WeekNum]),[Total Issued])

 

Date Table =

V_Date Table = 
ADDCOLUMNS (CALENDAR (DATE(2017,1,1), DATE(2025,12,31)),
"DateAsInteger", FORMAT ( [Date], "DDMMYYYY" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "MM-YYYY" ),
"YearMonthShort", FORMAT ( [Date], "mmm-YYYY" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"WeekNum",WEEKNUM([Date],2))

Total Issued:

Total Issued = DISTINCTCOUNT(R_Issued[Reference])

[Reference] is just a unique reference code for each ticket.

 

Snapshot of current DAX not working (for a date range of the last 6 months, e.g.):

Capture.PNG

 

Does anyone have a way to calculate this, (or know where I am going wrong), so that it works with any date range?

 

Any help will be really appreciated! 🙂

 

Many thanks,

 

Helen

2 ACCEPTED SOLUTIONS
OwenAuger
Super User
Super User

Hi @HelenBrunyee 

A few ideas here:

1. Yes, since Week Numbers repeat in different years, you won't get an accurate result by iterating over those within AVERAGEX.

I would recommend adding a column Week Start to your Date Table, with an expression like this (that would treat each week as beginning on a Sunday), or some variation:

 

[Date] - WEEKDAY ( [Date],2 )

 

Then rewrite your measure as:

 

Average Issued = AVERAGEX ( VALUES( 'V_Date Table'[WeekStart] ), [Total Issued] )

 

2. Something to watch out for: AVERAGEX ignores blanks when averaging, and you could have blanks if a week had no tickets issued at all within a week at a particular location. So a safer measure would be:

 

Average Issued = AVERAGEX ( VALUES( 'V_Date Table'[WeekStart] ), [Total Issued] + 0 )

 

While I generally wouldn't recommend turning blank measure results into zero in this way, in this case it's acceptable as we have a known number of weeks that we are iterating over and we need to use zero in the calculation.

 

3. Another option entirely is to write the measure as:

 

Average Issued =
DIVIDE (
    [Total Issued],
    COUNTROWS ( 'V_Date Table' )
) * 7

 

This avoids the problem of blank weeks in point 2, and will handle partial weeks appropriately. This assumes you would filter on a date range that doesn't include future (or past) dates that have no tickets issued.

 

Regards,

Owen

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

Hello Owen,

 

Thank you again for your help on this one, I was just going through your instructions here, and added the date count when I realised what the problem was - the Date filter was not linked up properly, and so not matching up to where I thought it was! 

 

I correctly linked up the Issued Date with the correct Date field in the the Date table, and made sure that the slicer used was the Date table 'Date', rather than from the Issued table and as if by magic, the average calculations are working perfectly! 🙂

 

Sorry about that, that was totally my fault not double checking the relationships and slicers!

 

Thanks again for your help - you're a life saver! 🙂

 

Best wishes,

Helen 

View solution in original post

5 REPLIES 5
OwenAuger
Super User
Super User

Hi again Helen,

 

You're welcome 🙂

That is a bit puzzling.

To try to diagnose, could you create a measure

 

Date Count = 
COUNTROWS ( 'V_Date Table' )

 

and add that to your table visual?

Since the numerator of the calculation seems to be working, I'm thinking there must be some problem with the denominator!

 

Alternatively, you could create some visuals that look at single location and add a WeekStart or similar field to the table to see if the values look correct at a weekly level.

 

Also, could you post a picture of your data model diagram view? Just wanting to check there isn't anything usual there.

 

Otherwise, if possible could you share a sanitised PBIX file (e.g. with dummy data but same model structure)?

 

Regards,

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Hello Owen,

 

Thank you again for your help on this one, I was just going through your instructions here, and added the date count when I realised what the problem was - the Date filter was not linked up properly, and so not matching up to where I thought it was! 

 

I correctly linked up the Issued Date with the correct Date field in the the Date table, and made sure that the slicer used was the Date table 'Date', rather than from the Issued table and as if by magic, the average calculations are working perfectly! 🙂

 

Sorry about that, that was totally my fault not double checking the relationships and slicers!

 

Thanks again for your help - you're a life saver! 🙂

 

Best wishes,

Helen 

That's great news, glad it's working now!

No worries at all - that's the nature of figuring these things out on a forum 🙂

 

I realise I just marked your last post as a solution by accident - but seems appropriate since the actual explanation of the issue was in your post!

 

All the best

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
OwenAuger
Super User
Super User

Hi @HelenBrunyee 

A few ideas here:

1. Yes, since Week Numbers repeat in different years, you won't get an accurate result by iterating over those within AVERAGEX.

I would recommend adding a column Week Start to your Date Table, with an expression like this (that would treat each week as beginning on a Sunday), or some variation:

 

[Date] - WEEKDAY ( [Date],2 )

 

Then rewrite your measure as:

 

Average Issued = AVERAGEX ( VALUES( 'V_Date Table'[WeekStart] ), [Total Issued] )

 

2. Something to watch out for: AVERAGEX ignores blanks when averaging, and you could have blanks if a week had no tickets issued at all within a week at a particular location. So a safer measure would be:

 

Average Issued = AVERAGEX ( VALUES( 'V_Date Table'[WeekStart] ), [Total Issued] + 0 )

 

While I generally wouldn't recommend turning blank measure results into zero in this way, in this case it's acceptable as we have a known number of weeks that we are iterating over and we need to use zero in the calculation.

 

3. Another option entirely is to write the measure as:

 

Average Issued =
DIVIDE (
    [Total Issued],
    COUNTROWS ( 'V_Date Table' )
) * 7

 

This avoids the problem of blank weeks in point 2, and will handle partial weeks appropriately. This assumes you would filter on a date range that doesn't include future (or past) dates that have no tickets issued.

 

Regards,

Owen

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Hi @OwenAuger ,

 

Thank you SO much for your help on this one, all your options made perfect sense 🙂

 

Only issue is, it doesn't seem to be working it out right. I could be being daft but in the example below, I have set the date range to Feb 2021, so 4 weeks. If the Total Issued is 200, shouldn't the average per week be around 50 (200 / 4)? I'm not sure where the 0.43 is coming from.. or how it is working that out. The column 'Test  Average Issued Weekly' is using your option 3 and 'Average Issued' is option 2:

 

Capture.PNG

 

Again, thank you - really appreciate your help on this!

 

Best,

Helen

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors