Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
How can i create a DAX formula to accomplish this:
Monday, 5/29/2017 I want to display 6/3/017 in a new column by adding 5days
Tuesday, 5/30/2017 I want to display 6/2/017 in a new column by adding 4days
...and keep doing it for Wed +3days, Thu +2days, Fri +1day every week for 5days only.
Any help is greatly appreciated. Thanks.
Solved! Go to Solution.
@n2p2gupta wrote:
Sorry, let me clarify:
I have the date/day column. I need to create below new column i,e, "new date"
Date/Day new date comments
6/5/2017, Monday 6/10/2017 +5days and displayed the date
6/6/2017, Tuesday 6/10/2017 +4days and display the date
6/7/2017, Wednesday 6/10/2017 +3days and display the date
Thanks
So it seems that your question is to find the next Saturday? If so, then try
new date = 'Table'[date]+7-WEEKDAY('Table'[date])
Or extractly match the logic
new date 2 = SWITCH ( WEEKDAY ( 'Table'[date] ), 1, 'Table'[date] + 6, //when sunday 2, 'Table'[date] + 5, //when monday 3, 'Table'[date] + 4, //when tuesday 4, 'Table'[date] + 3, //when wednesday 5, 'Table'[date] + 2, //when thursday 6, 'Table'[date] + 1, //when friday 'Table'[date] )
Not sure of the exact requirements here but perhaps this will get you close:
Column = =DATEADD(Table[Date],5-WEEDAY(Table[Date],3),year)
A 3 return type starts out as 0 for Monday, 1 Tuesday, etc. If you want to exclude this for Sat and Sun you would need to wrap this in an IF statement and could use WEEKDAY as your test condition. If you really only want this for 5 specific days, you could again wrap this in an IF statement testing for those days.
Sorry, let me clarify:
I have the date/day column. I need to create below new column i,e, "new date"
Date/Day new date comments
6/5/2017, Monday 6/10/2017 +5days and displayed the date
6/6/2017, Tuesday 6/10/2017 +4days and display the date
6/7/2017, Wednesday 6/10/2017 +3days and display the date
Thanks
@n2p2gupta wrote:
Sorry, let me clarify:
I have the date/day column. I need to create below new column i,e, "new date"
Date/Day new date comments
6/5/2017, Monday 6/10/2017 +5days and displayed the date
6/6/2017, Tuesday 6/10/2017 +4days and display the date
6/7/2017, Wednesday 6/10/2017 +3days and display the date
Thanks
So it seems that your question is to find the next Saturday? If so, then try
new date = 'Table'[date]+7-WEEKDAY('Table'[date])
Or extractly match the logic
new date 2 = SWITCH ( WEEKDAY ( 'Table'[date] ), 1, 'Table'[date] + 6, //when sunday 2, 'Table'[date] + 5, //when monday 3, 'Table'[date] + 4, //when tuesday 4, 'Table'[date] + 3, //when wednesday 5, 'Table'[date] + 2, //when thursday 6, 'Table'[date] + 1, //when friday 'Table'[date] )