Displaying Open, Closed, and Backlogged Work Orders

[PotatoMan]

Hello Everyone, 

I am trying to use a line and clustered column chart to help display some work orders of the last 6 months (using a slicer)

Currently I have two bars, one for completed work orders and one for open work orders. These are then broken down by months. 

I want to add a line showing what the backlog is of open work orders from before the slicer date to present day. 

I currently have a solution in place where I work out if there is an open work order, and then add it to an ongoing tally I created in a new column. However, I realise now that when a work order is complete it never shows a decrease in the backlog, it just lowers the overall backlog value, creating the illusion that the open work orders are only ever growing. 

Here is a sample of what I currenly get

Here is some Sample Data, any help would be greatly apreciated.  

[PotatoMan]

Hi, 

Thank you for this. 

I tried to replicate your suggested code and ran into a few errors. 

First: I tried to create a measure for the Cumulative Open Work Orders but it gave me an error at this stage

YourTable[Date] <= MAX(YourTable[Date])
&& YourTable[Status] = "Open"
)

it did not allow me to select YourTable[Status]. I tried to create a new column instead and this seemed to have worked. 

Next when I try to create a measure for the Net Change in Open Work Orders, It comes up with an error

"A single value for column 'Cumulative Open Work Orders' in table 'IW38 - Corr' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

When I try to make a new column I get the error

"A single value for column 'Cumulative Open Work Orders' in table 'IW38 - Corr' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

I have also tried to add "SUM" before both Cumulative Open Work Orders and recieve error

"<pi>A circular dependency was detected: IW38 - Corr[Net Change in Open Work Orders], IW38 - Corr[Cumulative Open Work Orders], IW38 - Corr[Net Change in Open Work Orders].</pi>"

I am not sure what the next steps would be.

[saud968]

1. Creating the Cumulative Open Work Orders Measure
To create a measure for the cumulative open work orders, you can use the following DAX formula:

Cumulative Open Work Orders =
CALCULATE(
COUNTROWS(YourTable),
FILTER(
ALL(YourTable),
YourTable[Date] <= MAX(YourTable[Date])
&& YourTable[Status] = "Open"
)
)
Make sure that YourTable[Status] is a valid column in your table. If it doesn't allow you to select it, double-check the column name and ensure it exists in the table.

2. Creating the Net Change in Open Work Orders Measure
For the net change in open work orders, you need to ensure that you're aggregating the values correctly. Here's a revised approach:

Net Change in Open Work Orders =
SUMX(
YourTable,
IF(
YourTable[Status] = "Open", 1,
IF(YourTable[Status] = "Completed", -1, 0)
)
)
3. Avoiding Circular Dependencies
Circular dependencies occur when measures or calculated columns depend on each other in a way that creates a loop. To avoid this, you need to ensure that your calculations are independent of each other.

Example Implementation
Create a Calculated Column for Status Change:

Status Change =
IF(
YourTable[Status] = "Open", 1,
IF(YourTable[Status] = "Completed", -1, 0)
)
Create a Measure for Cumulative Open Work Orders:

Cumulative Open Work Orders =
CALCULATE(
SUM(YourTable[Status Change]),
FILTER(
ALL(YourTable),
YourTable[Date] <= MAX(YourTable[Date])
)
)
Create a Measure for Net Change in Open Work Orders:

Net Change in Open Work Orders =
SUM(YourTable[Status Change])
Final Steps
Plot the Data: Use the Cumulative Open Work Orders measure as your line in the chart.
Bars for Monthly Changes: Use the Net Change in Open Work Orders measure for the bars representing monthly changes.

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

[saud968]

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!