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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
mattue84
New Member

Change sundays to mondays

ok with referals they come in all hours of the day. but we only work M-F with a few hours on Sat. so all the refureals on Sunday really dont get worked till monday. So I need to beable to push the ones that fall on Sun to the next day. So I can push all sunday referals to 8am no problem, but i need to also push the date from 3/20/2022 to 3/21/2022.

 

I do have a colum that has the day ot eh week so I can easly filter to only the sunday referals. 

 

This is needs to be done in Power Pivot. i would try a If and statment but cant get it to work or maybe just bad formula.

 

= Table.AddColumn(#"Expanded Dates", "Custom", each if [Dates.Day of Wk]= "Sun" then [List Details.TIMESTAMP]+0/1/0 else [List Details.TIMESTAMP])

 

 

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

Hi @mattue84 

 

What columns do you already have and what is the expected result?

 

According to the code you provided, it seems you want to add a column and add one day to dates falling on Sundays, right? If so, you can use Date.AddDays function. 

= Table.AddColumn(#"Added Custom", "Custom", each if [Day of Week] = "Sunday" then Date.AddDays([Date],1) else [Date])

vjingzhang_1-1648188025546.png

 

Or use [Date]+#duration(1,0,0,0) to get the same result. #duration function

= Table.AddColumn(#"Added Custom", "Custom", each if [Day of Week] = "Sunday" then [Date]+#duration(1,0,0,0) else [Date])

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

View solution in original post

2 REPLIES 2
v-jingzhang
Community Support
Community Support

Hi @mattue84 

 

What columns do you already have and what is the expected result?

 

According to the code you provided, it seems you want to add a column and add one day to dates falling on Sundays, right? If so, you can use Date.AddDays function. 

= Table.AddColumn(#"Added Custom", "Custom", each if [Day of Week] = "Sunday" then Date.AddDays([Date],1) else [Date])

vjingzhang_1-1648188025546.png

 

Or use [Date]+#duration(1,0,0,0) to get the same result. #duration function

= Table.AddColumn(#"Added Custom", "Custom", each if [Day of Week] = "Sunday" then [Date]+#duration(1,0,0,0) else [Date])

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

amitchandak
Super User
Super User

@mattue84 , in the previous step, where you setup day of week you can use additional parameter to set start of week as monday

https://docs.microsoft.com/en-us/powerquery-m/date-dayofweek

 

Date.DayOfWeek(#date(2011, 02, 21), Day.Monday)

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.