Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Whitewater100
Solution Sage
Solution Sage

Calculate First Day of Week

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 

1 ACCEPTED SOLUTION
4 REPLIES 4
Jihwan_Kim
Super User
Super User

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

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


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
amitchandak
Super User
Super User

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors