Calculate First Day of Week

[Whitewater100]

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 

[amitchandak · Accepted solution]

@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-6b2e...

.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-Powe...

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-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8

[Whitewater100]

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

[Jihwan_Kim]

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] )

[Whitewater100]

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