Forum Discussion

ZachUnger's avatar
ZachUnger
Helper II
3 years ago
Solved

Date Formatting

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.InspectionStatusScheduled DateColour
CR2000000Scheduled1/01/2022Backlog
CR2000001In process27/12/2022Reporting Month
CR2000002Scheduled28/01/2022Next Month
CR2000003Scheduled14/02/2023Upcoming

 

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

 

General Comment
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    3 years ago

     

    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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

     

    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_'s avatar
    vicky_
    Super User
    3 years ago

    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.