YOY Comparison when only weekly info is available

[Anonymous]

My data is by week, not by day. I have a table of week ending dates / week number / Month / Year etc. I have a combined YEARWK field that matches the year and week - so the most recent full week is 201627.

the dollar table has a matching YearWk field. This year is 201627, last year 201527, etc.

By filtering the week numbers I can get YTD to show, but can't figure out how to get the change %.

Every tip I have found is based on daily amounts. Any hints????

[v-sihou-msft]

@Anonymous

In DAX, there's no direct method to do YOY comparison on Week, which means PREVIOUSYEAR() function can't work for weeks. In this scenario, I assume your YearWeek column is a text column. You can substring the year part and minus 1 to get the last year, then concatenate into PreviousYear Week and lookup the corresponding value. Please see my sample below:

Create a calcualted column to get PreviousYear Week.

PreviousYear Week = CONCATENATE(left(Table8[YearWeek],4)-1,RIGHT(Table8[YearWeek],2))

Create a measure the get the PreviousYear Week Value.

PreviousYear Week Value = 
IF(CONTAINS(Table8,Table8[YearWeek],Table8[PreviousYear Week]),
LOOKUPVALUE(Table8[Value],Table8[YearWeek],Table8[PreviousYear Week]),
BLANK())

Regards,

[Anonymous]

I must be doing something wrong!! I am getting: Column 'PrevousYear Week' in table 'Blended' cannot be found or may not be used in this expression.

The column was added and does show when I put it on a view.  Can you tell from the screen print below what I did wrong???

[Anonymous]

Simon_Hou,

thanks but I guess I didn't explain myself well enough.

This is a sample of the date table:

And this is a sample the data file:

Ideally what I want is a YOY % change column for both the current week and the Year to date.

Since I have both year and week available, I have no problem showing same week sales,  what I can't do is get a % change.

Let's say this is the current week data:

this is what I would want to see for the change % for the week.

Appreciate any guidance you can give!!! Thanks!