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

 

[Anthony23]

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]

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

 

[Anthony23]

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]

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]

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]

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]

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]

No problem Anthony, glad I could help!

 

[Anthony23]

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]

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.