The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to 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
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
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.
User | Count |
---|---|
75 | |
70 | |
39 | |
30 | |
28 |
User | Count |
---|---|
104 | |
95 | |
51 | |
48 | |
46 |