Problem with same week Previous year

[Anonymous]

Hi,

We have an issue when trying to show data from previous year same week.

We understand that date from this year isn't in same week number previous year but would like to override that. Our data is organized week by week and entered in dataset that way (also I'm adding a sample dataset)

When we use SAMEPERIODLASTYEAR(Calendar[Date]) it looks for a Date and offset week (see in example), we also tried the calculation with WeekNumber = WeekNumber && Year = Year(TODAY()) -1 but it gives an error on last week W52 (see in example).

Is there a solution for these types of cases? We use ISO Calendar and is also added here (perhaps there is a solution for using weekly based Calendar table - which will also work for us, since our data is organized week by week).

Thanks,

Dragan

https://www.dropbox.com/s/3ul547c5goob2cw/Test%20Case.pbix?dl=0

[donsvensen]

Hi 

You can get inspiration here  

https://www.sqlbi.com/articles/week-based-time-intelligence-in-dax/

I typically use a slightly simpler approach by doing like this

SALES VALUE LY  = CALCULATE([Sales Value], DATEADD(Date[Date], -364, DAY))

-364 is equal to 52 weeks * 7

Requires as always a date table with all dates

/Erik

[Anonymous]

I've also tried this approach but the problem is when you look at year 2017 for instance,

you have 2016-W52 and 2017-W52, so if we are to use W52 for instance on axis we will double the data.

We overrided this by hiding 2016-W52, but I believe that there should be a stabile solution,

or it's just Ok to extract year from ISO week and use that as a year filter and not year filter from Calendar.

01.01.2017 is 2016-W52 and it's a Calendar year 2017, so the question is if we use ISO week and our data is entered week by week 

(every last week day) should we for filtering ignore Calendar and extract year filter from ISO week and use that as filter

Thanks

[donsvensen · Accepted solution]

Hi

You need to have a column in your date table with the ISO Year as well.

And create an ISO year Week Hierarchy - with iso year and iso week and date

BR

Erik

[Anonymous]

in this case we used 01st January 2017 as ISO year 2016 since it's W52_2016

and it gave correct result