A tricky visualisation / measure

[AC23VM]

Hi.  Can anyone please help me with visualising something.  I'm struggling to articulate what I'm trying to do, so please bear with me!

I have a dataset about rework activities.  I want to determine what rework is In Progress, Completed, Overdue, and Rolling.  The first 3 are relatively simple.

In Progress:

Rework Status = In Progress or Sent

Journey Outcome <> No Harm with Obvs 

Rework Deadline = Today or Any future date

Completed:

Rework Status = Completed

Journey Outcome <> No Harm with Obvs

Overdue:

Rework Status = In Progress or Sent

Journey Outcome <> No Harm with Obvs 

Rework Deadline = Before today's date

Where I'm falling down is Rolling.  Rolling is all of 'Overdue,' but also it's now past the 1st of the following month.  For example, if piece of rework went Overdue in December, and it's still Overdue in January, it's now both Overdue and Rolling.  

I've tried this as a column and line chart, where the X axis is the date the rework was requested, and the line is the rolling volume.  What ends up happening is it shows the rolling volume based on the month they were raised (because of the X axis).  

You can also see here, the rolling and overdue volumes are identical, so I'm not quite capturing the data I want.  This is where I'm having trouble articulating.  Rolling IS Overdue, but it's more trying to capture when they started rolling, and the fact that they are now part of a 'future' month's Overdue volume, i.e., if there are 14 Overdue in December, how many of those were raised and went overdue in December, and how many have rolled from previous months?

To get around the issue of them showing in the months they were requested, a more experienced colleague tried to add a blank calendar table and apply them to that, but it did this:

Below is what we tried to capture as Rolling:

Rolling - Deadline + 1 Month =

Date.AddMonths([#"Re-WorkDeadline"], 1)

Rolling - Date it becomes rolling =

Date.StartOfMonth([#"Rolling - Deadline + 1 Month"])

Rolling - Date it becomes rolling & Overdue - Sent / In Progress & Act / Pot Har =

if [#"Overdue”] = 1 then [~”Rolling - Date it becomes rolling”]

But it's just not quite working.  I made a dummy dataset if someone could help me, but I can't upload an xlsx file, so not sure how to add that.  Please feel free to hit me up with any questions or clarifications.

[AC23VM]

Does anyone know why applying a generic calendar has this effect?  I've messed around with the relationship between the two tables, but it doesn't seem to make any difference.  Not only does this not fix my initial issue of getting the Rolling figure to work, it breaks every other part of the visualisation.  I'm lost.

[AC23VM]

Thanks both.  I figured that out, made it, and this is the result:

If I just out both measures in the Y axis, it's this:

[Anonymous]

We are absolutely delighted to hear that the issue has been resolved. If you’re inclined, you might consider accepting this solution, as it could also assist other community members facing the same problem, enabling them to find a resolution more swiftly.
 

Of course, if there is anything else we can do for you, please do not hesitate to contact us.

Looking forward to your reply.

Best Regards,

Leroy Lu

[AC23VM]

Haha.  No, it hasn't been resolved.  That's not working.  That's me demonstrating that it doesn't work.  I already posted this problem in my original post.  You can see in the first image there are 14 Overdue in December.  Using the solution described here, that number has changed to 64, and then made those volumes uniform across every date.  It's clearly not working.

---

---

[bhanu_gautam]

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

[AC23VM]

Hi there,  Trying the RollingStatus gives me a 'Token Eof expected' error:

[Anonymous]

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