CODEWITHSUNDEEP
sqlpowerbidax
Amit
Amit

Reputation: 667

Power BI - How do you calculate first Day of Week?

So here is my scenario. If it's 20th week of the Year 2016, I need to know it's 5/8/2016. This is consistent for the rest of week. Same goes for all the week.

Week#   WeekDate
29     7/10/2016 0:00
30     7/17/2016 0:00
28     7/3/2016 0:00
31     7/24/2016 0:00
32     7/31/2016 0:00
33     8/7/2016 0:00
34     8/14/2016 0:00

Upvotes: 0

Views: 4846

Answers (2)

Amit
Amit

Reputation: 667

Thanks for the contribution, I did something like below and it worked. This is keeping Sunday as the first day of week.

If you want to use Monday or Tuesday, add +2, +3 in the WeekNum calculation below.

DATE(SampleTableWithDate.Year],1,1) - WEEKDAY(DATE(SampleTableWithDate.Year,1,1),1)  + (WEEKNUM(SampleTableWithDate.Date)-1)*7 + 1

Find the First Date of this Year    -  First Day of Week This Year                   + Add Weeks till Date

Upvotes: 0

CoSpringsGuy
CoSpringsGuy

Reputation: 1645

dateadd(DAY,-(datepart(weekday,DATEADD(WEEK,30-1,CAST('2016'||'-1-1' AS DATE)))-1),DATEADD(WEEK,30-1,CAST('2016'||'-1-1' AS DATE)))

This works with Intersystems. You will have to construct your own date with the known year value and replace '30' with the known week value from your table

Upvotes: 1

Related Questions