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

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.