Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 fir...
Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    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.

HotChilli's avatar
HotChilli
Icon for Community Champion rankCommunity Champion
4 years ago

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.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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).