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

[Anthony23]

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]

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]

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.

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]

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]

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]

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]

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]

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

 

[Anthony23]

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]

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
    )
)