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

[HelenBrunyee]

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.):

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

[OwenAuger · Accepted solution]

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

[OwenAuger]

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

[HelenBrunyee · Accepted solution]

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 

[OwenAuger]

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

[OwenAuger · Accepted solution]

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

[HelenBrunyee]

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:

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

Best,

Helen