# Weekly data compared to  previous week and YOY same week- 1, 4 ,12 and 52 week



## Anthony23 (Aug 19, 2014)

I am working with weekly data in a financial year from July-June, which i have numbered the weeks from the beginning of the data (named WeekSinceStart in equations)and also got weeks 1-52/53 for the individual years.

I would like to show the following:

Current week sales (ie 1st jan 2014(numbered week 1)) 
The Last 4 weeks sales (from the current week ie 1st jan 2014)
The last 12 weeks sales (from the current week ie 1st jan 2014)
The last 52 weeks sales (from the current week ie 1st jan 2014)

As well as comparing growth from:

The previous year same week(ie 1st Jan 2013 compared to 1st Jan 2014(numbered week 1)
Last 4 weeks, and the preious year 4 weeks (same period) 
Previous year 12 weeks (same period)

I have come up with 2 equations, the first- which should compare the same week 52 weeks ago, or 1 week, 4 weeks or 12 weeks depending if I cahnge the -52 at the end:

Sales same week 52 weeks ago (With my table names in):

Sales same week 52 weeks ago:=CALCULATE([UnitSales], Dates[WeekSinceStart]=values(Dates[WeekSinceStart])-52)

However I get the following error:

“A table of multiple values was supplied where a single value was expected”

The second equation, which should sum the last 52 weeks, or 12,4 or 1 week depending if I change the -52::

Sales same last 52 weeks sales:=CALCULATE([UnitSales], Dates[WeekSinceStart] >= values(Dates[WeekSinceStart]-52)
&& Dates[WeekSinceStart] <= values(Dates[WeekSinceStart]))

However I get the following error:

“Semantic Error:The Values function expects a column reference for argument ’1′, but a string or numeric expression was used”

To add confusion to this issue, my financial years are July-June

I look forward to your resposne, and any assistance you can give


----------



## scottsen (Aug 19, 2014)

I would think you could use the built in time intelligence for some of this, like SAMEPERIODLASTYEAR(), DATESQTD, etc.

But to answer your question, what is probably happening is the Grand Total cells are giving you trauma, since they will have more than once WeekSinceStart.

Any time you are using VALUES(), it is probably best to "protect" it via   IF (HASONEVALUE(column), ...

Though, typically how I write these looks more like:
DatesYTD:=CALCULATE([UnitSales], FILTER(ALL(Calendar), Calendar[Date] <= MAX(Calendar[Date]))

Using max, as opposed to values.


----------



## Anthony23 (Aug 19, 2014)

scottsen said:


> I would think you could use the built in time intelligence for some of this, like SAMEPERIODLASTYEAR(), DATESQTD, etc.
> 
> But to answer your question, what is probably happening is the Grand Total cells are giving you trauma, since they will have more than once WeekSinceStart.
> 
> ...



Hello, and thank you for the response.

If I used the sameaslastperiod and then dateqtr it wouldnt pull out the information I needed would it? As the dateqtr would pull out yearly quarters (ie q1 jan-march)..whereas I want a column with weeks and then the last 4 weeks from that week, last 12, and last 52..then a comparison to the same week (same week number from year out of 52) same 4 weeks and same 12 weeks from the previous year to work out how for example the last 4 weeks sales from week 1 1st july 2014 compared to the same 4 weeks sales from the previous year..

I would like a table to have week 1 (1st july 2014) week 2, week 3 ect then current week sales, prior week (so week before) and previous yeae same week, then same for 4 and 12 weeks from the week 1, week 2 ect dates..

Sorry if I have repeated myself in this comment..

Thanks against for the response


----------



## scottsen (Aug 19, 2014)

Gotcha.  There may till be places where built ins are nice for you -- your calendar isn't custom enough to "break" them (like a 445 Calendar), but I see now you aren't interested in like "month to date" measure... you want "the previous 4 weeks ignoring the silly calendar".  

Did the rest of my answer re: VALUES make sense?


----------



## Anthony23 (Aug 19, 2014)

Hi scottsen

Yes that is correct..so for every week I want the previous 4 weeks, previous 12 weeks and 52 from that week..that is why I numbered all the weeks from the beginnjng of the data. I also then want to compare that week, 4,12 and 52 with the same week (the initial every week) from the previous year. 

Interms of the values..it did, I think so for my equation I would do:

=calculate ([unitsales]), filter(all (dates), dates [weeksincestart]<=Max (dates [weeksincestart]))

Weeksincestart is the column for a week number for every week of data from the beginning of the data. How would I tell this formula to sum last 4 and compare that with same 4 weeks from previous year..

Sorry if this is simple, I just cannot get my head round it. 

Thanks


----------



## scottsen (Aug 19, 2014)

No apology necessary, none of it came easy to me in the beginning 

I'm not super clear on how you calc/use WeeksSinceStart, but hopefully this gets you moving anyway 

I am going to just pretend you have a WeekNumber (where 0 would be "the beginning of time" in your calendar)...

Prev4WeekUnits := calculate ([unitsales]), filter(all (dates), dates [WeekNumber] <=Max(dates[WeekNumber]) &&
     dates[WeekNumber] > MAX(dates[WeekNumber] - 4)))

So, <= Max[WeekNumber] is "biggest weeknumber in the current pivot cell".  If you have weeks on row, that is "current week", if you have month, it would be "last week in month".  Hot.   You want all Unit Sales less than or equal to that week... and...

You want to constraint the low end to not be more than 4 weeks ago.     >MAX(dates[WeekNumber] - 4  should handle that for you.  (and 52, etc).


----------



## Anthony23 (Aug 19, 2014)

Hello Scottsen

Thanks for the response. I inputted the equation, but an error flag appeared stating "Semantic Error:The MAX function only accepts a column reference as an argument"

What does this mean? Does this mean that rather than completing the measure in the powerpivot window I do ti as a function in the pivot table?

Thanks again, I can sense this is really close to be cracked

Thanks


----------



## scottsen (Aug 19, 2014)

I have max(column-4) in there. It should be max(column)-4


----------



## Anthony23 (Aug 19, 2014)

Hello Scottsen

Excellent it works!

AS I have done this on the week number column, I will have to play around to make it display in my table as the year and then date.

Now I have completed half of my query. Being able to calculate the last 1 week, 4, 12 and 52- by changing the elements on your equation. How do I then compare this to the same period last year. So on week 58 on my weeknumber column (week sincee data began) how do I compare the 4 weeks I have just calculated with 4 weeks from week 6 (minus the 52 weeks in the year).So i am comparing the same period YOY if that makes sense.

Much appreciated, that equation is amazing, and just what I wanted..I was close initally, but the change to MAXa nd applying the filter was the key.

Thanks


----------



## scottsen (Aug 19, 2014)

Well, I would certainly try:   =CALCULATE([Last4Weeks], SAMEPERIODLASTYEAR(Dates[Date]))

Cuz it is easy, and should work, and I am going to be confused when you tell me it doesn't.   Once THAT happens...   It is just adjust the start/end filters...


```
Prev4WeekUnits-PY :=
CALCULATE (
    [unitsales],
    FILTER (
        ALL ( dates ),
        dates[WeekNumber] <= MAX ( dates[WeekNumber] ) - 52 && 
        dates[WeekNumber] > MAX ( dates[WeekNumber] ) - 56
    )
)
```


----------



## Anthony23 (Aug 19, 2014)

I am working with weekly data in a financial year from July-June, which i have numbered the weeks from the beginning of the data (named WeekSinceStart in equations)and also got weeks 1-52/53 for the individual years.

I would like to show the following:

Current week sales (ie 1st jan 2014(numbered week 1)) 
The Last 4 weeks sales (from the current week ie 1st jan 2014)
The last 12 weeks sales (from the current week ie 1st jan 2014)
The last 52 weeks sales (from the current week ie 1st jan 2014)

As well as comparing growth from:

The previous year same week(ie 1st Jan 2013 compared to 1st Jan 2014(numbered week 1)
Last 4 weeks, and the preious year 4 weeks (same period) 
Previous year 12 weeks (same period)

I have come up with 2 equations, the first- which should compare the same week 52 weeks ago, or 1 week, 4 weeks or 12 weeks depending if I cahnge the -52 at the end:

Sales same week 52 weeks ago (With my table names in):

Sales same week 52 weeks ago:=CALCULATE([UnitSales], Dates[WeekSinceStart]=values(Dates[WeekSinceStart])-52)

However I get the following error:

“A table of multiple values was supplied where a single value was expected”

The second equation, which should sum the last 52 weeks, or 12,4 or 1 week depending if I change the -52::

Sales same last 52 weeks sales:=CALCULATE([UnitSales], Dates[WeekSinceStart] >= values(Dates[WeekSinceStart]-52)
&& Dates[WeekSinceStart] <= values(Dates[WeekSinceStart]))

However I get the following error:

“Semantic Error:The Values function expects a column reference for argument ’1′, but a string or numeric expression was used”

To add confusion to this issue, my financial years are July-June

I look forward to your resposne, and any assistance you can give


----------



## Anthony23 (Aug 20, 2014)

Hello Scottsen

that is great- just what i wanted! 

Thanks for your help on this matter- you have been amazing.

In regards to my other thread which you commented on, sorry it was a bit vague, but what i was tryign to get to was to compare the growth in the table you have helped me contruct.. I now have previous 4 weeks from each week- i want the growth between week 4's last 4 week performance and week 5's 4 week performance for example. 

Once again cheers!


----------



## scottsen (Aug 20, 2014)

You would need to write a measure that is "previous week's 4 week total" then use that in the growth measure.


----------



## Anthony23 (Aug 20, 2014)

scottsen said:


> You would need to write a measure that is "previous week's 4 week total" then use that in the growth measure.



Hi again..I thought that might be the case.. 

in regard to the equation you constructed for me titled prev4weekunits it calculates the previous four weeks including the current week.if I wanted it to calculate the previous 4 weeks excluding the current week do I just alter the equation at the first max (weeknumber]-1)


Sorry I am on my phone and away from my pc. 

Many thanks, your help has been amazing


----------



## scottsen (Aug 20, 2014)

You can either create a version of the measure from scratch that filters to weeks 2-5 or use (probably better), base your new measure on the old one, and use DATEADD.

4WeekTotal-PriorWeek := CALCULATE([4WeekTotal], *DATEADD(Calendar[Dates], -7, Day)*)


----------



## Anthony23 (Aug 21, 2014)

Hello Scottsen

Thanks for that. I see how that equation would have worked. I may have missrepresented what I needed however.

The equation you created for me caluclates the last 4 weeks from the current week, which i need and is called week end. While I also need prior 4 weeks, so the four weeks before and not including the current week. 

so if current week is week 1 - week end includes 1, -1,-2, -3
whereas - prior week includes -1,-2,-3,-4.

i have looked at the initial measure you created:

 Units4weekend:=calculate ([unitsales], filter(all (dates), dates [WeekSinceStart] <=Max(Dates[WeekSinceStart]) &&
Dates[WeekSinceStart] > MAX(dates[WeekSinceStart]) - 4))


and wondered if what i needed to do was add a -1 around the weeksincestart.

it is frustrating, as once this is done, I am all set!

Also i have done the growth we discussed about.

once again

Thanks


----------



## scottsen (Aug 21, 2014)

We can certainly write out the measure "verbose" like you are doing, though, I'm not clear on why the DATEADD won't work for you. I think your 0 based vs 1 based number scheme is throwing me off 

DATEADD(Calendar[Dates], -7, Day) just means "shift this context back 1 week" (kinda regardless of what the measure itself is doing).  So, a filter that happens to be over Weeks 5-11 becomes 4-10 (though, that is backwards from your "Weeks Since" concept).


----------



## Anthony23 (Aug 21, 2014)

Hello Scottsen

Silly me. It does work!

Just inputted it again, and the DATEADD does work.

I have realised that I have current week from a Sum=(unitSales) and then it is in a table of weeks. However to get previous week I tried:

:= CALCULATE([UnitSales], *DATEADD(Calendar[Dates], -7, Day)

*But this gave the next week rather than the previous.

I tried to adapt the following equation

Units1weekend:=calculate ([unitsales], filter(all (dates), dates [WeekSinceStart] <=Max(Dates[WeekSinceStart]) &&
Dates[WeekSinceStart] > MAX(dates[WeekSinceStart]) - 1))


more out of hope than expectationa s I knew it wouldnt work.

I have learnt alot from you, and have ended up with something I thought I wouldnt be able to get to, so thanks


----------



## scottsen (Aug 22, 2014)

No problem Anthony, glad I could help!


----------



## Anthony23 (Aug 22, 2014)

scottsen said:


> No problem Anthony, glad I could help!



Hello (again  )

I still cannot get the previous week equation, I really thought It would be =calculate([unitsales]), dateadd ([calendar[dates],-7, day)  but like I said that does the next week rather than previous..I cannot understand why. 

Also a query. Alot of the equations have used filter all, and the pivot table with sliders is running  quite abit slower now, would that be the filter running through all the data or the use of kpi's do u believe? I have read around and apparently the use of filters can slow down the reports. Do you know of any way of speeding it up. Just thought id ask. 

Thanks


----------



## scottsen (Aug 23, 2014)

At this point, I would need to see the workbook.  If you can share it on OneDrive/GoogleDrive/Dropbox, I can take a look.  If not, we can probably arrange a quick consult.

There are tons of things that can impact perf.  Generally a FILTER() is NOT going to be one of them (and that generally means you are processing LESS data, which is good).

My only guess on the Next vs Previous week is that you do operations on WeeksSinceStart which ... feels backwards to me.  But, again, would need to see it.


----------



## Anthony23 (Aug 19, 2014)

I am working with weekly data in a financial year from July-June, which i have numbered the weeks from the beginning of the data (named WeekSinceStart in equations)and also got weeks 1-52/53 for the individual years.

I would like to show the following:

Current week sales (ie 1st jan 2014(numbered week 1)) 
The Last 4 weeks sales (from the current week ie 1st jan 2014)
The last 12 weeks sales (from the current week ie 1st jan 2014)
The last 52 weeks sales (from the current week ie 1st jan 2014)

As well as comparing growth from:

The previous year same week(ie 1st Jan 2013 compared to 1st Jan 2014(numbered week 1)
Last 4 weeks, and the preious year 4 weeks (same period) 
Previous year 12 weeks (same period)

I have come up with 2 equations, the first- which should compare the same week 52 weeks ago, or 1 week, 4 weeks or 12 weeks depending if I cahnge the -52 at the end:

Sales same week 52 weeks ago (With my table names in):

Sales same week 52 weeks ago:=CALCULATE([UnitSales], Dates[WeekSinceStart]=values(Dates[WeekSinceStart])-52)

However I get the following error:

“A table of multiple values was supplied where a single value was expected”

The second equation, which should sum the last 52 weeks, or 12,4 or 1 week depending if I change the -52::

Sales same last 52 weeks sales:=CALCULATE([UnitSales], Dates[WeekSinceStart] >= values(Dates[WeekSinceStart]-52)
&& Dates[WeekSinceStart] <= values(Dates[WeekSinceStart]))

However I get the following error:

“Semantic Error:The Values function expects a column reference for argument ’1′, but a string or numeric expression was used”

To add confusion to this issue, my financial years are July-June

I look forward to your resposne, and any assistance you can give


----------



## marco.russo (Aug 24, 2014)

Sorry for joining the party late. I would just highlight two general approaches to do YOY with non-standard calendars, when you cannot use standard time intelligence functions.
A general description of the probem is included in Time Patterns article (Time Patterns – Dax Patterns) - if you look at the PY Sales measure, you see that the idea behind is to get the list of days in a month and retrieve the same selection in the same days one year before (please note you might have different "logical month" than the "physical month" that you have in that date - e.g. First of July belonging to "logical" June month). One important point addressed by PY Sales formula is what to do when only a few days are selected in a period (e.g. only Mon-Fri of each week).
In case of weeks, you might use the same approach (like you probably did in the examples discussed in this thread), but for weeks there is a simpler pattern you can use when you have 445 or similar calendars (Week-Based Time Intelligence in DAX – SQLBI). 
I hope these other examples can help!


----------



## Anthony23 (Aug 25, 2014)

Hello Scottsen

I have managed to speed it up.. by removing the kpi status measures (the icons) it has dramatically improved the performance. I believe by using multiple kpi status icon measures it produces a lag. TO be fair I had probably used them excessively. 

In regards to my query: current v previous week- I am still not gettjng the answer I want. Like described, for some reason instead of comparing current v previous it calculates current v next??


----------



## Anthony23 (Aug 25, 2014)

Hello marco.russo

Thanks for the articlea, they were an interesting read. There are some good formulas I may use in the future in there. I feel that the approach used is the correct one for what I need, but the 445 may come in use later. 

Its a same that powerpivot doesnt come with time function weeks, like it does for months and qurters. 

Thanks


----------



## marco.russo (Aug 25, 2014)

The reason why there are no weeks function is because there are too many variations (certain countries start the week on Monday, other on Sunday, other on Saturday; week number 1 in one year might be different; week number 53 in one year might be different; even companies who are using ISO weeks might use different "custom" calendars for legacy reasons).
Time Intelligence functions handle comparison over years, and any minimal difference in start of year could bring to completely different results. 
I experienced these differences first hand for many customers as a consultant and I understand why there are no week-related time intelligence functions in DAX - in order to be simple, they have to be without too many arguments (the YTD has a third optional argument for end-of-year, and I have seen people misusing it).


----------



## scottsen (Aug 25, 2014)

Funny, I thought of it differently.  "Months are variable, but weeks are always 7 days... so customers NEED extra help on months, where for weeks... they can just subtract 7".


----------



## marco.russo (Aug 26, 2014)

For one week before - yes. For the same week one year ago, sometime you have to subtract 364 days, sometime 371 days. And you have to handle also selection of non-contiguous days.


----------



## Anthony23 (Aug 26, 2014)

Heelo Scottsen

just re-read my last post, it came across abit rude. I was refering to the equation not giving me the answer I wanted.. rather than you guys! 

It now works though

Thanks


----------



## marco.russo (Aug 26, 2014)

No problem, it wasn't rude!


----------

