Forum Discussion
Switch Function for Tollgate Dates
Can anyone help me trouble shoot the issue I'm having with this Power BI formula? It's worked perfectly all year but now dates are going into next year I'm having issues. I have a column with the first date of the week in it and use this switch function to work out the start and end date for each tollgates.
The issue I’m having is when the dates are into next year it’s not calculating them up correctly. Please see attached screenshots.
Hi, Anonymous ;
You could modify it.
End = SWITCH ( 'Table'[Tollgate], "Tollgate 4", 'Table'[week date] + 139, "Tollgate 3", 'Table'[week date] + 83, "Tollgate 2", 'Table'[week date] + 55, "Tollgate 1", 'Table'[week date] + 27, "Tollgate 0", 'Table'[week date] + 6 )Or use calendar date.
The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
DATEADD can only return dates which are already in the date table being used. You are using the inbuilt date functionality so I'm thinking that the dates returned do not exist in these tables.
People usually handle this sort of thing by using a Dates table. You can control the dates yourself using a separate dates table.
-----
You could also try using + instead of dateadd to get the new dates. Give that a go first.
HotChilli @I will try that.
I already have a calendar table but I couldn't figure out how to use that because I need to calculate the tollgate from the current weeks Monday (ie 6/12/2021 this week).
Hi, Anonymous ;
You could modify it.
End = SWITCH ( 'Table'[Tollgate], "Tollgate 4", 'Table'[week date] + 139, "Tollgate 3", 'Table'[week date] + 83, "Tollgate 2", 'Table'[week date] + 55, "Tollgate 1", 'Table'[week date] + 27, "Tollgate 0", 'Table'[week date] + 6 )Or use calendar date.
The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
