Help with ISO dates!!

[Bobbys]

Hello everyone,

I'm trying to set up a report for my company and am facing difficulties calculating correct YTD and YTD-1 measures due to the fact that the date format in the data source is odd.

As seen in the picture above, the data source shows data for week 52 of 2022, which should be impossible. This is most likely due to week 52 2021 going into 2022, thus being identified as 2022. I've attemped to create a custom date column (due to the fact that my data doesn't include any specific dates, only week number & year), by combining year and week number into a column, then using this formula to create dates:

Dato =

VAR __Year = VALUE(right([weekyear],4))

VAR __Week = VALUE(left([Weekyear],2))

VAR __Calendar = ADDCOLUMNS(CALENDAR(DATE(__Year,1,1),DATE(__Year,12,31)),"Week",WEEKNUM([Date]))

RETURN

MINX(FILTER(__Calendar,[Week]=__Week),[Date])

This however, for obvious reasons, gives me dates and values for december 2022, as seen in the picture under:

 I found a date format which appears to show correct year and week in hopes that I can make some sort of relation between the "week year" columns in my main query and my date table, but haven't been able to figure out how to do it. This is the date table I created:

 

 Please, any help is well appreciated!

[Anonymous · Accepted solution]

HI @Bobbys,

I'd like to suggest you create a calendar table with the custom 'year week' field to link to the current table fields. Then you can use the related date field values for YTD calculations.

Regards,

Xiaoxin Sheng

[amitchandak]

@Bobbys , Try if this can help

New column = Weeknum([Date],21)

[Bobbys]

Hello @amitchandak. Okay, I've made the column. How should I proceed?
Thank you