Last Year & Rolling Period Sales Calculation (using adjusted dates)

[bellspartan23]

Hello,

I know that Power BI has been great with time intelligence capabilities/functions; using SAMEPERIODLASTYEAR, etc. However, in my situation, 53 weeks in some years creates a bit of an issue because of YOY change. I'm sure this is nothing new; with the 53rd week not lapping anything and nothing lapping it from 1 year to the next. Though, in my case, I've taken steps to override the standard WEEKNUM() function through Power Query.

Here is the approach I've taken:

I duplicated the table, removed all columns (except the date column), removed duplicate dates, sorted Ascending, added in an index column where I then applied a Number.Mod function (custom column) to have the Wk # reset once it reaches 52. The final step is merging the tables to pull in the Adjusted Week #. This might not be the best approach but it delivers the right output.

A couple of side notes on why this was done: 

The data I am provided is weekly, not daily. Data has been adjusted to Saturday end dates to maintain consistency, due to the number of customers/data sources involved. All of the data is being delivered via Excel and stored in SharePoint to correctly assign the weeks.

I'm trying to calculate last year measures using the adjusted weeks and year. Here is an example of a calculated measure I created:

LY $ = 

var selectedyr = CALCULATE(MAX('Total Sales'[Adj Year])-1) //Return max year and substract 1 year

var maxwkend = MAX('Total Sales'[W.E.]) //Return max week

var maxwk = CALCULATE(MAX('Total Sales'[Adj Week #]),'Target Total Sales'[W.E.] = maxwkend) //Return adjusted week # where date matches week

return

CALCULATE([Sales $],

REMOVEFILTERS('Total Sales'[W.E.]), //Remove filters on W.E. dates within table

'Total Sales'[Adj Year]=selectedyr, //Year is equal to 1 year prior

'Total Sales'[Adj Week #]=maxwk) 

This does exactly what I need it to do by week but it doesn't aggregate in the totals based on the "Max Week" condition; just pulls in the LY sales for the latest week (makes sense). 'Calculated Column' seems like the better approach so it can be aggregated but I'm struggling with the translation. Would you be able to assist? Thanks!

[bellspartan23]

Hi Devanshi,

Thanks for sending. I just want to make sure I understand. Is this meant to be an alternative approach, where instead of calculating LY sales within 1 'date' column we are just creating 2 different date columns to break out the sales calculations? Is there not a way to do this using the same 'date' column? Also, I noticed that the "Week" parameter doesn't exist within the 'DATEADD' function. I can definitely explore this route instead but was hoping to use the same 'Date' column to calculate TY and LY sales. Thanks!

[devanshi]

VAR currentyear = SELECTEDVALUE('Table'[Year])
VAR currentweek = SELECTEDVALUE('Table'[Week])
VAR Lastyear = currentyear - 1
RETURN
CALCULATE([Sales], FILTER( ALL('Table'),
                                               'Table[Year] = Lastyear && 'Table[Week] = currentweek))

[bellspartan23]

Hi Devanshi, Really appreciate the quick response and help here! In this case, I'm trying to avoid having the SELECTEDVALUE function be used because I want the measure to be dynamic. Basically, I'm just trying to get a Last Year Sales column that applies the logic to every row within the dataset (Calculated Column) based on the Adj Week # and Adjusted Year.

I included an example below to help illustrate. This is using the logic in the Calculated Measure above but I want to do it as a Calculated Column to be able to aggregate in the totals/subtotals.

.

[devanshi]

Try this,
LastYearDate =
VAR CurrentWeekNumber = WEEKNUM('Table'[DateColumn])
VAR CurrentYear = YEAR('Table'[DateColumn])
VAR LastYear = CurrentYear - 1
RETURN
DATEADD('Table'[DateColumn], -52 + CurrentWeekNumber, WEEK) - MOD('Table'[DateColumn], 7) + (WEEKNUM(DATE(LastYear, 12, 31)) - 1) * 7