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

 

[marco.russo]

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]

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]

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]

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]

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]

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]

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]

No problem, it wasn't rude!