Help with Conditional Column (Date Issue)

[fbarini]

Hello, I'm having trouble to find a condition formula to my column.

I'm trying to track Helpdesk Backlogs, but I just can't find a solution to format the new column. From my POV, it's a simple problem, but as a somewhat beginner I'm suffering a bit to make things work properly

I have a column named "Creation Date" which should be enough to classify all the rows. Here are the following rules:

1. If Service Request was created between May/01/2021 and May/31/2021, it should be "Inside Month"

2. If service Request was created between Apr/01/2021 and Apr/30/2021, it should return "30 days"

3. If service Request was created on or before Mar/31/2021, it should return "60 days"

It might seems obvious, but this formula needs to be inteligent, since I want to autoupdate it everyday as months pass by.

I tried to start with the following formula just to test the first rule, but it didn't count correctly and it also counts May/2020. I also have no idea on how to write the 2. and 3. rules.

Any help is aprecciated, thanks in advance.

[jdbuchanan71 · Accepted solution]

@fbarini 

Try something using DATEDIFF like this.

Backlog 2 = 
VAR _Today = TODAY()
VAR _Months = DATEDIFF(BaseChamados[Data de Criacao],_Today,MONTH)
RETURN 
SWITCH(
    _Months,
    0,"Inside Month",
    1,"30 days",
    2,"60 days",
    3,"90 days",
    "91+ days"
)