topic Re: Calculate First Day of Week in DAX Commands and Tips

Calculate First Day of Week

Whitewater100 — Tue, 05 Jul 2022 20:55:14 GMT

Hello Community:

I am trying to have a card showing "Report for Week Beginning". Normally it would be a Monday, unless a Holiday was on Monday, then this would show Tuesday's date. The issue I am having is the logic works for Monday & Tuesday but when Wednesday comes it falls part.

I have Relative Date Slicer set to "This Day".

In the end the report should show the Start of Week date staying constant on Monday or Tuesday(if Monday is a holiday) for the entire week until next week starts, and then repeats the same process. I will attach my file which has several attempts butit is not working as expected.

Any input is very much appreciated!

Thank you.https://drive.google.com/file/d/1y4EtLgL1XrxSnn0145EYYPVGRs31_sw9/view?usp=sharing

Re: Calculate First Day of Week

amitchandak — Wed, 06 Jul 2022 03:50:54 GMT

Whitewater100 , I doubt you can change the week start date of the relative date slicer. Only regional setting can make diff

You can have any start date of week using this code in your calendar

https://medium.com/chandakamit/cheat-sheet-any-weekdays-week-start-date-just-one-variable-apart-6b2e6f593958

.Any Weekday Week - Start From Any day of Week
https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Power-BI-Turning/ba-p/1187482

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8

Re: Calculate First Day of Week

Jihwan_Kim — Wed, 06 Jul 2022 03:51:54 GMT

Hi,

I am not sure if I understood your Dates1 table correctly, but please try the below measure, and put it into a card visualization.

New measure: = VAR _weekstartdate = MINX ( FILTER ( Dates1, Dates1[Date] = TODAY () ), Dates1[FirstDateOfWeek] ) VAR _thisweektable = FILTER ( Dates1, Dates1[FirstDateOfWeek] = _weekstartdate && Dates1[Holiday] <> 1 ) RETURN MINX ( _thisweektable, Dates1[Date] )

Re: Calculate First Day of Week

Whitewater100 — Wed, 06 Jul 2022 13:05:29 GMT

Hi Jihwan:

Thank you very much for working on this. I tried the solution but it shows today's date7-06. Since Monday was a holiday, the result would stay at 7-05(Tuesday) for this week.

I found a solution like this:

Report Start Date =

var _selectedYear = SELECTEDVALUE(Dates1[year])

var _selectedWeek = SELECTEDVALUE(Dates1[Week No.])

var _startOfTheWeek =

CALCULATE(

FIRSTDATE(Dates1[Date]),

FILTER(ALL(Dates1),

Dates1[Week No.] = _selectedWeek && Dates1[year] = _selectedYear && Dates1[Flag] = "WeekStart")

)

return

_startOfTheWeek

Re: Calculate First Day of Week

Whitewater100 — Wed, 06 Jul 2022 13:07:11 GMT

Hi amitchandak:

Thanks very much for replying. I checked out the articles and have a solution like this:

Report Start Date =

var _selectedYear = SELECTEDVALUE(Dates1[year])

var _selectedWeek = SELECTEDVALUE(Dates1[Week No.])

var _startOfTheWeek =

CALCULATE(

FIRSTDATE(Dates1[Date]),

FILTER(ALL(Dates1),

Dates1[Week No.] = _selectedWeek && Dates1[year] = _selectedYear && Dates1[Flag] = "WeekStart")

)

return

_startOfTheWeek

I appreciate the leads! Thanks again