CODEWITHSUNDEEP
powerbidax
shashbin30
shashbin30

Reputation: 39

Custom Week Column in Power BI starting with conditions in StartofMonth and EndofMonth

I'm trying to create a Week column in Dax Calendar table but haven't got any luck.

Conditions: 1.Week starts from Saturday and ends on Friday. 2.If the date is starting on the month beginning then the week should start with it and same goes for month end.

DATE WEEK Explanation
1/1/2024 1 Jan'24 - 5 Jan'24 Week starts on 1st day of Month on Monday and week ends on Friday
1/2/2024 1 Jan'24 - 5 Jan'24 Week starts on 1st day of Month on Monday and week ends on Friday
1/3/2024 1 Jan'24 - 5 Jan'24 Week starts on 1st day of Month on Monday and week ends on Friday
1/4/2024 1 Jan'24 - 5 Jan'24 Week starts on 1st day of Month on Monday and week ends on Friday
1/5/2024 1 Jan'24 - 5 Jan'24 Week starts on 1st day of Month on Monday and week ends on Friday
1/6/2024 6 Jan'24 - 12 Jan'24 Week starts on 6th and ends on 12th
1/7/2024 6 Jan'24 - 12 Jan'24 Week starts on 6th and ends on 12th
1/8/2024 6 Jan'24 - 12 Jan'24 Week starts on 6th and ends on 12th
1/9/2024 6 Jan'24 - 12 Jan'24 Week starts on 6th and ends on 12th
1/10/2024 6 Jan'24 - 12 Jan'24 Week starts on 6th and ends on 12th
1/11/2024 6 Jan'24 - 12 Jan'24 Week starts on 6th and ends on 12th
1/12/2024 6 Jan'24 - 12 Jan'24 Week starts on 6th and ends on 12th
1/13/2024 13 Jan'24 - 19 Jan'24 Week starts on 13th and ends on 19th
1/14/2024 13 Jan'24 - 19 Jan'24 Week starts on 13th and ends on 19th
1/15/2024 13 Jan'24 - 19 Jan'24 Week starts on 13th and ends on 19th
1/16/2024 13 Jan'24 - 19 Jan'24 Week starts on 13th and ends on 19th
1/17/2024 13 Jan'24 - 19 Jan'24 Week starts on 13th and ends on 19th
1/18/2024 13 Jan'24 - 19 Jan'24 Week starts on 13th and ends on 19th
1/19/2024 13 Jan'24 - 19 Jan'24 Week starts on 13th and ends on 19th
1/20/2024 20 Jan'24 - 26 Jan'24 Week starts on 20th and ends on 26th
1/21/2024 20 Jan'24 - 26 Jan'24 Week starts on 20th and ends on 26th
1/22/2024 20 Jan'24 - 26 Jan'24 Week starts on 20th and ends on 26th
1/23/2024 20 Jan'24 - 26 Jan'24 Week starts on 20th and ends on 26th
1/24/2024 20 Jan'24 - 26 Jan'24 Week starts on 20th and ends on 26th
1/25/2024 20 Jan'24 - 26 Jan'24 Week starts on 20th and ends on 26th
1/26/2024 20 Jan'24 - 26 Jan'24 Week starts on 20th and ends on 26th
1/27/2024 27 Jan'24 - 31 Jan'24 Week starts on 27th and ends on the last day of the month i.e 31st
1/28/2024 27 Jan'24 - 31 Jan'24 Week starts on 27th and ends on the last day of the month i.e 31st
1/29/2024 27 Jan'24 - 31 Jan'24 Week starts on 27th and ends on the last day of the month i.e 31st
1/30/2024 27 Jan'24 - 31 Jan'24 Week starts on 27th and ends on the last day of the month i.e 31st
1/31/2024 27 Jan'24 - 31 Jan'24 Week starts on 27th and ends on the last day of the month i.e 31st

Upvotes: 1

Views: 49

Answers (1)

Sam Nseir
Sam Nseir

Reputation: 12111

Try the following DAX Calculated Column:

WEEK = 
  var mnthStart = EOMONTH([Date], -1) + 1
  var mnthEnd = EOMONTH([Date], 0)
  var doWk = WEEKDAY( [Date], 2)
  var prevSat = [Date] - doWk - IF(doWk < 6, 1, -6)
  var nextFri = prevSat + 6
  var wkStart = IF(prevSat < mnthStart, mnthStart, prevSat)
  var wkEnd = IF(nextFri > mnthEnd, mnthEnd, nextFri)
  var dateFormat = "d MMM'yy"
  return FORMAT(wkStart, dateFormat) & " - " & FORMAT(wkEnd, dateFormat)

Upvotes: 0

Related Questions