Forum Discussion
A tricky visualisation / measure
AC23VM , Try using
Create a calculated column to determine if a rework item is rolling
RollingStatus =
VAR OverdueDate = [Rework Deadline]
VAR CurrentDate = TODAY()
VAR FirstOfNextMonth = EOMONTH(OverdueDate, 0) + 1
RETURN
IF(
[Rework Status] IN {"In Progress", "Sent"} &&
[Journey Outcome] <> "No Harm with Obvs" &&
OverdueDate < CurrentDate &&
CurrentDate >= FirstOfNextMonth,
"Rolling",
BLANK()
)
Ensure you have a calendar table to manage dates effectively
Calendar =
ADDCOLUMNS(
CALENDAR(MIN('ReworkData'[Rework Deadline]), MAX('ReworkData'[Rework Deadline])),
"Year", YEAR([Date]),
"Month", MONTH([Date]),
"MonthName", FORMAT([Date], "MMMM"),
"YearMonth", FORMAT([Date], "YYYY-MM")
)
Create measures to calculate the rolling volumes based on the defined rolling status
RollingVolume =
CALCULATE(
COUNTROWS('ReworkData'),
'ReworkData'[RollingStatus] = "Rolling"
)
OverdueVolume =
CALCULATE(
COUNTROWS('ReworkData'),
'ReworkData'[Rework Status] IN {"In Progress", "Sent"} &&
'ReworkData'[Journey Outcome] <> "No Harm with Obvs" &&
'ReworkData'[Rework Deadline] < TODAY()
)
X-Axis: Use the YearMonth column from the calendar table.
Y-Axis: Use the RollingVolume and OverdueVolume measures.
Hi there, Trying the RollingStatus gives me a 'Token Eof expected' error:
Thanks for the reply from bhanu_gautam , please allow me to provide another insight:
Hi, AC23VM
Thanks for reaching out to the Microsoft fabric community forum.This is because your [Rework Deadline] is a column name. In a calculated column, you can try entering a single quote (') first to get a prompt. This will help you reference the column name more accurately and avoid errors.
Of course, if you have any new discoveries or questions, please feel free to get in touch with us.
Best Regards,
Leroy Lu
