Year to Year Growth by Fiscal Weeks

By in ,
2692
Year to Year Growth by Fiscal Weeks

Although calculating year to year growth simply can be done by using DAX formulas such as SamePeriodLastYear() or DateAdd(), but  for some business  that tracking their metrics on weekly base a different approach is required. In this post I explain  how to demonstrate year on year growth on weekly base.

First we need to understand how the fiscal years can be split into weeks. Different countries have different start and end dates for their fiscal year. The country I discuss about it in this post is Australia that the fiscal year is from 1st of July to 30th June next year.

As dividing the  number of days in a year(365) to the number of days in a week(7) gives 52 whole weeks  and 1 day as remainder, the first year will be from July 1 to June 29, gets 52 weeks and passes 1 day to  next year. The second year is from June 30 to June 28 , gets 52 weeks and passes 2 days to the following years. Considering there are 366 days in the leap years, the 6th  or 5th year  will have  371 days that is 53 weeks.So we will have 5 or 6 years (depends on how many leap years will be in the date range) with 52 weeks and one year with 53 weeks as below:

This image has an empty alt attribute; its file name is t4cjE1xleawRdx_dJ9Di3UzhVW1-dn-SFmAnLidkpwdrWBNeC6mi66NM7nTPeBbDcS7Po7A6OkJZ6VfZjeAC9-EMSMi26fcNGzppgeYUVU8Zgc8qqWIPa_rzb5-gLIf1gF_G_Rk

Obviously Date table must be customised by a sql script to include the above logic that is not in the scope of this post.

To create a demo report in Power BI w need to have a fact table linked to our Date dimension.Here I have used InternetSales table from AdventureWork database:

This image has an empty alt attribute; its file name is U9OMSw2VEE5BMQcLOc-utr0a8semmq7dLHN9yDrB44HC4_Z8CQVcouXaS8OTHGbJ7gzx96EY6q-7fz8gmtN_5sDhwikd4NUXrTMoIGzqwzqoOKvH-jmjwVr-jM32lB2AO2ZmY-Q

First let create a measure for last year sales amount based on SamePeriodLastYear():


Sales Amount LY = CALCULATE(Sales[Sales Amount],SAMEPERIODLASTYEAR('Date'[Date]))


As expected this measure works fine when date table is filtered by calendar year but for fiscal year the values don’t match:

This image has an empty alt attribute; its file name is V-gZvd8icCsgWC6UO4YT-xgasAWy0QVMat1BUj9nxy2v2pteFtCX8rrAuUDXGdrfWOq35xYf2vFRIEr_y8FB-tELNTnvF0b2PJIxiaKc3mDpWiQA7NChY9bIiDMGduk5zoSKMqs

A different formula is needed here to calculate the metric at weekly level and then aggregate it to yearly.The below DAX formula gives the sales amount for the same week of the last year:

Sales Amount LY by Week =
 var TY=SELECTEDVALUE('Date'[FYear])
 var TW=SELECTEDVALUE('Date'[FWeek])
 return
 CALCULATE(Sales[Sales Amount],
 FILTER(All('Date'),
         'Date'[FYear] = TY -1 && 'Date'[FWeek]=TW))


This image has an empty alt attribute; its file name is 0_2nRmag8EBt8bQHWCV_fHxK4UibvEIqXRXXzFmr0RSsI4w9yo0C06DMEjsRBhuofv06ykIfYKf1v_TlG2D2UGWcsC83G1go69ODiF_kAcab4T_wOvNwm15mMnrxjYlRsjUqurE

As you can see the sales amount of last year for each week is correct but  the total value is blank and also when FWeek column is removed from the table the measure will be blank:

This image has an empty alt attribute; its file name is UR25OIn1tz9x-0QXu87YtkJcG51HZYwqGvDhvnMkeJqgSCEVErgxUhwMC6W5-2u3YVUmZ9gQuYpibVcEB9dd9Dv5OLvWdIRFVKHEFhuHpbOc8T4bw_9ltnMp5Eu_ieZ6QrLBsiU

This behaviour is due to using SELECTEDVALUE() for FWeek in the formula that expects to have a single  value of FWeek. Using VALUES() instead of SELECTEDVALUE() gets the existing weeks in a date range even without having FWeek as a filter:

Sales Amount LY by Weeks =
 var TY=SELECTEDVALUE('Date'[FYear])
 var Weeks=VALUES('Date'[FWeek])
 return
 CALCULATE(Sales[Sales Amount],
                       FILTER(All('Date'),
                         'Date'[FYear] = TY -1),
       Weeks)

This change fixes the total issue and also the correct values for can be seen on year level:

This image has an empty alt attribute; its file name is mEv3Lf4VnFDJUoWJ0E7YhF55L7CbjQYJMizPglKnG9Wax_QJt4PKixUGegTlmWu5QchtkSHw8Eu405_dQ1zJpYJvKO92etpEE1HIJplvkAJyODejG8OKgoWNxj3GJQ7N7TiTiL4

This image has an empty alt attribute; its file name is SKY3LKzbThBoh61uFl1j1CTVYUNo9t_93L7FM0NZluUSnE7Vbtf2tuzjud6xmepeYN8_T2l9MbeORtBd2Yw7uOermF3b3XLuvRaz6mJghCSrLnzJt8eVecxC6w6LHJnzd4YOME0

By this measure, you can also select a range of weeks and see the last year amount for them without including FWeek in the visual:

This image has an empty alt attribute; its file name is VMFLzOxxMa0a-IMSL0UY_BDHPrTziSyHGs9s38QLLjVA96yHAXxBoUSiT3FdjDHA35SPWN8AblMYiqNjOg2VZGCpJPMc6OKkim4_MSyWEQf5GKyoOVEfiEPbkB9Rcd8TJwL-cVo

Now we can calculate year on year growth:


Sales Growth% = if(ISBLANK(Sales[Sales Amount]),BLANK(),
         DIVIDE(Sales[Sales Amount]-Sales[Sales Amount LY by Weeks],
               Sales[Sales Amount LY by Weeks]))


This image has an empty alt attribute; its file name is pG8kd6Nwgt8BS_p3ROhB-BPF57SQE3rTjewoBPIJK8xhdNseWHbTjktUerkcyZp57vKsYSP3rsqRgN0LttYRvxgw-6ot3A6NoSTwsvjjCQdqhy4fSHt-A4GovBpH_CDm_R1xygA

Leave a reply

Your email address will not be published. Required fields are marked *