Problem with generated date and new year

[ChrWil92]

Dear Fabric Community,

I have a problem with my semantic model. The input data has not date, but a date snapshot column with the following format.

202452

202501

The first four characters is equal to the year, the next two characters are equal to the fiscal week. For visualization purpose I generate a theoretical date value with the following custom function.

= (Year as number, Week as number) as date =>
let
offsetToISO = Date.AddDays(#date(Year,1,1),-4),
dayOfWeek = Date.DayOfWeek(offsetToISO, Day.Monday),
offset = -dayOfWeek + (Week * 7),
isoWeekDate = Date.AddDays(offsetToISO, offset)
in
isoWeekDate

Now it's new year and for the actual snapshot 202501 it recognizes the date 30/DEC/2024 what is causing issues with the visualization as I see the wrong week. I can't just change the day of the custom function from Monday to Sunday as the filter of the visualization is then not working correctly anymore. If I use Sunday for example the generated date is in the future and the filter is excluding it from the visualization - As our team needs the data at beginning of the week, this is not a propper solution.

I hope I described my problem statement sufficient. Do you maybe know a more elegant solution to fix this?

Thanks in advance!

Best regards,

Chris

[ChrWil92 · Accepted solution]

I solved it now with adding another costum function in Power Query.

= Table.AddColumn(#"Zusammengeführte Spalte eingefügt", "Date_", each if Number.ToText([Year]) <> Text.End(Date.ToText([Date]), 4) then Text.Replace(Date.ToText([Date]), Date.ToText([Date]), "01/01/" & Number.ToText([Year])) else (Date.ToText([Date])))

When the snapshot says 2025, but the year of the date is different it is replacing the date to 01/01/Year from Snapshot. That should also solve it in future.