Displaying Open, Closed, and Backlogged Work Orders

To accurately display the backlog, we need to calculate the net change in open work orders over time. Here's a suggested approach using Power BI:

1. Data Preparation

Date Column: Ensure you have a Date column with the date of the work order.
Status Column: Have a Status column indicating whether the work order is 'Open' or 'Closed'.
2. Creating a Measure for Backlog

Cumulative Open Work Orders:

Cumulative Open Work Orders = CALCULATE(
COUNTROWS(YourTable),
FILTER(
ALLSELECTED(YourTable[Date]),
YourTable[Date] <= MAX(YourTable[Date])
&& YourTable[Status] = "Open"
)
)

Net Change in Open Work Orders:

Net Change in Open Work Orders =
VAR PreviousDate = CALCULATE(MAX(YourTable[Date]), DATEADD(YourTable[Date], -1, DAY))
RETURN
CALCULATE(
[Cumulative Open Work Orders],
YourTable[Date] = MAX(YourTable[Date])
)
-
CALCULATE(
[Cumulative Open Work Orders],
YourTable[Date] = PreviousDate
)

3. Visualizing the Data

Use a line chart for the Net Change in Open Work Orders measure.
Use clustered column charts for the Open Work Orders and Closed Work Orders counts.
Apply the desired date slicer to filter the visualization.
Explanation:

The Cumulative Open Work Orders measure calculates the total number of open work orders up to a specific date.
The Net Change in Open Work Orders measure calculates the difference between the current cumulative open work orders and the previous day's cumulative open work orders, providing a true representation of the backlog change.

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!