cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
rjs2
Resolver I
Resolver I

Change a date to the following Monday if it falls on a weekend

I am trying to evaluate a date field and change the date to the following Monday if it falls on a weekend.  I tried to use the WEEKDAY and DATEADD in an IF statement.  I keep getting :

 

"A Date Column containing duplicate dates was specified in a call o function 'DATEADD'.  This is not supported."

 

This is my statement:

Date Received = IF(WEEKDAY('Table'[DATE.FIELD],1)=1,Dateadd('Table'[DATE.FIELD],1,DAY))
 
I was going to do an embedend IF statement to calculate the Saturday and Sunday days to be the following monday, else use the date.
 
 
1 ACCEPTED SOLUTION
rjs2
Resolver I
Resolver I

solved it:

Created a weekday column then used this in a new column:

Date Received = If('Table'[Weekday]=7,'Table'[DATE]+2, If('Vetting Query'[Weekday]=1,'Table'[DATE]+1,'Table'[DATE]))

View solution in original post

1 REPLY 1
rjs2
Resolver I
Resolver I

solved it:

Created a weekday column then used this in a new column:

Date Received = If('Table'[Weekday]=7,'Table'[DATE]+2, If('Vetting Query'[Weekday]=1,'Table'[DATE]+1,'Table'[DATE]))

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors