Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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.
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.
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).
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |