Forum Discussion
Getting Date from Week Number and Year
Hi,
I have a fact table in which I have Week Number and Year wise Sales
I want to create a relationship of fact table with my calendar Table (Which have all dates from Year 2020 and 2021)
I'm confused why the StartDate (of Week 53 2020) is repeating? (it should have only 1 row as 12/28/2020) as I have only 1 row for that week.
Help?
It's working well 🙂
13 Replies
Hey MH3 ,
your question is difficult to answer, as it is not clear how the columns are calculated.
Next to that, it's unusual that a fact table contains these columns, a fact table should only contain the SalesDate column, then this column will be connected to your date table.
The DAX statement below creates a table containing the information of three dates:Table = var Date1 = DATE( 2020 , 12 , 27 ) var Date2 = DATE( 2020 , 12 , 28 ) var Date3 = DATE( 2021 , 1 , 2 ) return UNION( ROW( "date" , Date1 , "WeekNumber ISO" , WEEKNUM( Date1 , 21 ) //the parameter 21 returns the isoweeknumber ) , ROW( "date" , Date2 , "WeekNumber ISO" , WEEKNUM( Date2 , 21 ) //the parameter 21 returns the isoweeknumber ) , ROW( "date" , Date3 , "WeekNumber ISO" , WEEKNUM( Date3 , 21 ) //the parameter 21 returns the isoweeknumber ) )​Maybe that helps to understand what's going on.
Regards,
TomActually, The data was given on Week-Year basis
so I had splitted the week and year= (TheYear as number, TheWeek as number) as date => let //test //TheYear = 2018, //TheWeek = 1, // offsetToISO = Date.AddDays(#date(TheYear,1,1),-4), dayOfWeek = Date.DayOfWeek(offsetToISO, Day.Monday), offset = -dayOfWeek + (TheWeek * 7), isoWeekDate = Date.AddDays(offsetToISO, offset) in isoWeekDate
and the StartWeek I had calculated by this FunctionHey MH3 ,
please take the time and create a pbix that contains sample data, including your M functions. Upload the pbix to onedrive or dropbox and share the link.
Regards,
Tom
How are you calculating the date in the fact table?
On a simple level you're getting repeated start of week dates because you have an over lap between week 53 of 2020 and week 1 of 2021. Eg it's the same week but first few days are 2020 and last few are 2021. Effectively the start of week date is actually correct because weeks don't line up perfectly with years.
bcdobbs Well I had used that
Date.StartOfWeek(Date.AddDays(#date([Year],1,1), ([WeekNumber]-1)*7))
But now I had used a function like this= (TheYear as number, TheWeek as number) as date => let //test //TheYear = 2018, //TheWeek = 1, // offsetToISO = Date.AddDays(#date(TheYear,1,1),-4), dayOfWeek = Date.DayOfWeek(offsetToISO, Day.Monday), offset = -dayOfWeek + (TheWeek * 7), isoWeekDate = Date.AddDays(offsetToISO, offset) in isoWeekDate
Now, my StartDateWeek shows like this
is it Correct or not?Can I join Calendar (Date Column) with my Fact (StartDateWeek) ??
I think both "could" be correct. Depends on how weeks were originally defined in your source data. Personally suspect your later suggestion is better.
As far as a join to a date table yes, it'll work fine.
Hi MH3
See these links:
https://www.vahiddm.com/post/creating-calendar-table-with-3-steps
https://www.vahiddm.com/post/weekly-time-intelligence-dax
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/Hi MH3,
Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or accept the helpful suggestions to help others who faced similar requirements.
If these also don't help, please share more detailed information to help us clarify your scenario to test.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
