Forum Discussion
Function 'DATEADD' only works with contiguous date selections - WoW sales calculation
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.
Community ChampionUnfortunately, 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.
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.