cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

``````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 2
Anonymous
Not applicable

``````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

Super User

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors