Function 'DATEADD' only works with contiguous date selections - WoW sales calculation

[mur2za]

Hello all,

Trying to calculate WoW Change for my sales for the most recent two weeks. This works fine when I have less than one year of data. However, the moment I add in prior year data for the same month into the data model, the calculations throw out goofy numbers, and applying filters to the products gives the contiguous date error.

Here are my measures:

SalesToDate = SUM([Sales])

SalesToWeekPrior = CALCULATE([SalesToDate], DATEADD([SaleDate], -7, DAY)

SalesTo2WeeksPrior = CALCULATE([SalesToDate], DATEADD([SaleDate], -14, DAY)

SalesLastWeek = [SalesToDate] - [SalesToWeekPrior]

SalesPreviousWeek = [SalesToWeekPrior] - [SalesTo2WeeksPrior]

WoWChange = ([SalesLastWeek]/[SalesPreviousWeek])-1 (formatted as %)

Any ideas how to get around the non contiguous dates error? I tried the HASONEVALUE function as well to calculate SalesToWeekPrior, but it just returns a Blank.

Thanks!

Mur2za.

[kcantor]

Unfortunately, weekly calculations are more tedious than monthly or yearly due to the fuluctuation of weeks through out the year. To overcome the non-contiguous date selections I add a custom column to my date table that is a combination of Year and week. This is simply calculated as =[Year]&"-"&FORMAT([WeekNumber],"00") to produce this type of result: 2016-12. this allows the calculations to cross over the year mark and remain contiguously correct. this works with standard weeknumbers but gets a little hinky if you use ISO week numbers.

Just make sure you use the Year-Week on the axis instead of week numbers.

[mur2za · Accepted solution]

Thanks guys!

@kcantor, I tried doing the custom column like you suggested, but it didn't work for me - I was probably making some rookie mistake. However, I figured it out by just creating a page level date filter that restricted all the visualizations and calculations on that page to the last 2 weeks, so no longer getting the contiguous dates error. Not the most elegant solution, but it worked!

Thanks to @Sean and @GilesWalker for showing me this solution on a related issue, which I was able to apply to this problem... Here's what I did:

First, create the following measures and columns in the Calendar file:

1. Column - WeekEnding = ((7-DayInWeek) + DateKey)

2. Measure - Today = DATE(YEAR(NOW()), MONTH(NOW()), DAY(NOW()))

3. Column - Last14Days = IF(AND(Calendar[WeekEnding]>=Calendar[Today]-14, Calendar[WeekEnding]<=Calendar[Today]),1,0)

Next, create the following measures in your data file:

1. SalesToDate = SUM(SalesTable[Sales])

2. SalesPreviousWeek = CALCULATE([SalesToDate], DATEADD(SalesTable[SaleDate], -7, DAY))

3. SalesLastWeek = SalesTable[SalesToDate] - SalesTable[SalesPreviousWeek]

4. WoWSalesChange = (SalesTable[SalesLastWeek]/SalesTable[SalesPreviousWeek])-1....... format this as percentage.

Now, use Last14Days as a Visual (or if you need to apply to multiple elements, then Page level) Filter. Boom - no more contiguous date error!

Thanks again!

Mur2za.

[kcantor]

Sometimes you  just have to do what you can to make it work. Many of us are still learning as we go and our solutions get more elegant as we learn.

@mur2za Mark your reply with your workaround as your solution. That way if someone else has the same question they can find your work around.

[MattAllington]

The reason HASONEVALUE returns blank is because it doesn't have one value - that is what it is for - to prevent errors being thrown when multiple values are returned in an invalid scenario.  What answer are you expecting when you have a data for a single month but multiple years?