Backgrounds
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 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 the total issued – see snapshot below) and I think this is 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?
Solutions
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' )
) * 7This 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.
Ref: https://community.powerbi.com/t5/DAX-Commands-and-Tips/DAX-Formula-for-Weekly-Average-to-work-with-Date-Range-slicer/m-p/1739487#M36119