Getting Date from Week Number and Year

[MH3]

 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?

@amitchandak @ImkeF 

[Anonymous]

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

[VahidDM]

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/

[TomMartens]

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,
Tom

[MH3]

Actually, 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 Function 

[Syndicate_Admin]

Hi
@Syndicate_Admin
This function worked for me to calculate StartWeek

Thanks!

[TomMartens]

Hey @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

[MH3]

@TomMartens  I have sent you a private message kindly go through it.

[bcdobbs]

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.

[MH3]

@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) ?? 

[mike_honey]

I just stumbled across this hunting for an example ISO week => Date conversion. But the code doesn't look right if these are ISO weeks - WRT wikipedia the starting point is finding out the weekday of Jan 4, but the code above examines Jan 1.
https://en.wikipedia.org/wiki/ISO_week_date#Calculating_an_ordinal_or_month_date_from_a_week_date

[Syndicate_Admin · Accepted solution]

It's working well 🙂

[bcdobbs]

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.

[MH3]

@bcdobbs 
Hi

Actually, I have been given a University assignment could you please help me?