Date Formatting

[ZachUnger]

Hi Team I'm working with the below data set and want to add a column with the bold results based on the scheduled date. 

Is there anyone that would know how to do this?? 

Consent No.	InspectionStatus	Scheduled Date	Colour
CR2000000	Scheduled	1/01/2022	Backlog
CR2000001	In process	27/12/2022	Reporting Month
CR2000002	Scheduled	28/01/2022	Next Month
CR2000003	Scheduled	14/02/2023	Upcoming

Backlog = anything prior to reporting month

Reporting Month = anything with a date of the current month 

Next month = anything in the next month after reporting month 

Upcoming = anything after the next month 

Unsure if this can be done but keen on a solution if anyone has one. 

Cheer 

Z

[transform99 · Accepted solution]

Colour = 
VAR __CurrentMonthReporting =
    DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 )
VAR __NextMonthReporting =
    EOMONTH ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 ), 0 ) + 1
VAR __2MonthsReporting =
    EOMONTH ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 ), 1 ) + 1

RETURN
    IF (
        [Scheduled Date] < __CurrentMonthReporting,
        "Backlog",
        IF (
            AND (
                [Scheduled Date] >= __CurrentMonthReporting,
                [Scheduled Date] < __NextMonthReporting
            ),
            "Reporting Month",
            IF (
                AND (
                    [Scheduled Date] >= __NextMonthReporting,
                    [Scheduled Date] < __2MonthsReporting
                ),
                "Next Month",
                "Upcoming"
            )
        )
    )

Credit goes to previously answered questions (Found googling). I combined current/next/2month reporting views by declaring them as variables, and then used nested if statements.

Solved: How to express Next Month First Day, Prev. Month F... - Microsoft Power BI Community

[vicky_]

Try this:

New Column = SWITCH( TRUE(),
   MONTH([Scheduled Date]) = MONTH(TODAY()), "Reporting Month",
   MONTH([Scheduled Date]) < MONTH(TODAY()), "Backlog",
   MONTH([Scheduled Date]) = MONTH(TODAY()) + 1, "Next Month",
   "Upcoming"
)
   

You will probably need to tweak the above a little to get exactly what you need.