{"id":4038,"date":"2022-02-22T20:17:34","date_gmt":"2022-02-23T04:17:34","guid":{"rendered":"https:\/\/SUMMALAI.COM\/?p=4038"},"modified":"2022-02-22T20:17:36","modified_gmt":"2022-02-23T04:17:36","slug":"how-to-calculate-weekly-average-with-dax-formulas","status":"publish","type":"post","link":"https:\/\/SUMMALAI.COM\/?p=4038","title":{"rendered":"How to Calculate Weekly Average with DAX Formulas"},"content":{"rendered":"\n

Backgrounds<\/strong><\/p>\n\n\n\n

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.<\/p>\n\n\n\n

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).<\/p>\n\n\n\n

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:<\/p>\n\n\n\n

Average Issued = AVERAGEX(VALUES( 'V_Date Table'[WeekNum]),[Total Issued])<\/pre>\n\n\n\n
Date Table =<\/strong><\/p>\n\n\n\n
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))<\/pre>\n\n\n\n
Total Issued:<\/strong><\/p>\n\n\n\n
Total Issued = DISTINCTCOUNT(R_Issued[Reference])<\/pre>\n\n\n\n
[Reference] is just a unique reference code for each ticket.<\/em><\/p>\n\n\n\n
Snapshot of current DAX not working (for a date range of the last 6 months, e.g.):<\/strong><\/p>\n\n\n\n
\"Capture.PNG\"<\/figure>\n\n\n\n
Does anyone have a way to calculate this, (or know where I am going wrong), so that it works with any date range?<\/p>\n\n\n\n
Solutions<\/strong><\/p>\n\n\n\n
A few ideas here:<\/p>\n\n\n\n
1. Yes, since Week Numbers repeat in different years, you won’t get an accurate result by iterating over those within AVERAGEX.<\/p>\n\n\n\n
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:<\/p>\n\n\n\n
[Date] - WEEKDAY ( [Date],2 )<\/code><\/pre>\n\n\n\n
Then rewrite your measure as:<\/p>\n\n\n\n
Average Issued = AVERAGEX ( VALUES( 'V_Date Table'[WeekStart] ), [Total Issued] )<\/code><\/pre>\n\n\n\n
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:<\/p>\n\n\n\n
Average Issued = AVERAGEX ( VALUES( 'V_Date Table'[WeekStart] ), [Total Issued] + 0 )<\/code><\/pre>\n\n\n\n
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.<\/p>\n\n\n\n
3. Another option entirely is to write the measure as:<\/p>\n\n\n\n
Average Issued =\nDIVIDE (\n    [Total Issued],\n    COUNTROWS ( 'V_Date Table' )\n) * 7<\/code><\/pre>\n\n\n\n
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.<\/p>\n\n\n\n
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<\/p>\n","protected":false},"excerpt":{"rendered":"
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). Read More<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[10,621],"tags":[],"class_list":["post-4038","post","type-post","status-publish","format-standard","hentry","category-microsoft","category-power-bi"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/4038","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=4038"}],"version-history":[{"count":2,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/4038\/revisions"}],"predecessor-version":[{"id":4040,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/4038\/revisions\/4040"}],"wp:attachment":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=4038"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=4038"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=4038"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}