How to create a running total with baseline and filters

[SamuelBurt]

The main goal of this is to create a forcast of data given an amount of stock and how that stock will change.

Say I have an orignal amount, I want to subtract based on one status and then add based on another over time.
If we have three status "Current", "Due for Replacement" and "Need to be Repaced"
And we orignally have 100 "Due for Replacement"

If we start on 01/01/2024
Then 5 stock changes from "Current" to "Due for Replacement" on 05/01/2024
On the 05/01/2024 the total "Due for Replacement" will equal 105
This would then stay at this value until 15/05/2024 where 10 stock moves from "Due for Replacement" to "Need to be Replaced"
Meaning the total on the 15/01/2024 would be 95
Say then a few days later on 25/01/2024 10 stock changes from "Current" to "Due for Replacement" and 5 stock moves from "Due for Replacement" to "Need to be Replaced"
After all of this the value for "Due for Replacement" should once again equal 100.

I know this is more of a complicated forcast graph but Ive been struggling a little to get my head around how I could do this and show it on a graph.

Any help would be appreciated 🙂

[123abc]

To create a running total with baseline and filters in Power BI using DAX, you can follow these steps:

1. Create a Date Table: Ensure you have a date table that covers the period of your forecast. This table should have a column for dates (Date) and can include additional columns like year, month, day, etc.

2. Define your Data Model: Ensure your dataset includes a table with the following columns: Date, Status, and Stock Change. The Stock Change column indicates the change in stock for each status on a specific date.

3. Create Calculated Columns: You need to calculate the net change in stock for each status based on the Stock Change column. For example:

NetChange =
VAR StatusChange = IF('Table'[Status] = "Current", 0, IF('Table'[Status] = "Due for Replacement", 'Table'[Stock Change], -'Table'[Stock Change]))
RETURN
CALCULATE(SUM('Table'[NetChange]), FILTER('Table', 'Table'[Date] <= EARLIER('Table'[Date]))) + StatusChange

1. This DAX expression calculates the net change in stock by summing up the changes in stock for each status up to the current date.

2. Create Measures: You can create measures to calculate the running total for each status. For example:

RunningTotal_DueForReplacement =
CALCULATE(SUM('Table'[NetChange]), FILTER(ALLSELECTED('Table'), 'Table'[Status] = "Due for Replacement"))

1. This measure calculates the running total of stock for the "Due for Replacement" status.

2. Visualize on a Graph: You can use a line chart or an area chart to visualize the running total of each status over time. Place the date on the x-axis and the running total measure on the y-axis. You can add filters to the visualizations to focus on specific statuses if needed.

By following these steps, you should be able to create a running total with baseline and filters in Power BI using DAX. Make sure to adjust the column and table names in the DAX expressions according to your actual dataset.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[SamuelBurt]

Whilst not exactly what I was looking for this did help a bunch in calculating what I needed, thank you.

[SamuelBurt]

One step im stuck on is creating the net change, given the calculation you have given it creates a circular dependancy specifically SUM('Table'[NetChange]) in the NetChange column.

Is this what is expected if so how do I get around that?

[123abc · Accepted solution]

If you're encountering a circular dependency issue with the calculation of the net change, you may need to approach it differently. Circular dependencies occur when a formula refers to its own cell either directly or indirectly.

To calculate the net change, you can try a different approach. Instead of directly referencing the running total measure within the same calculation, you can calculate the net change separately. Here's how you can modify your DAX measures:

1. Running Total Measure: Keep the running total measure as previously defined.

2. Net Change Measure: Create a new measure to calculate the net change for each status. You can use SUMX and FILTER to iterate through the table and calculate the net change based on the status.

Here's an example of how you can define the net change measure:

Net Change =
VAR CurrentDate = MAX('YourDateTable'[Date])
RETURN
CALCULATE(
SUM('YourStockTable'[Amount]),
FILTER(
ALL('YourDateTable'),
'YourDateTable'[Date] = CurrentDate
),
FILTER(
ALL('YourStatusTable'),
'YourStatusTable'[Status] = "Due for Replacement"
)
)
+
CALCULATE(
SUM('YourStockTable'[Amount]),
FILTER(
ALL('YourDateTable'),
'YourDateTable'[Date] = CurrentDate
),
FILTER(
ALL('YourStatusTable'),
'YourStatusTable'[Status] = "Need to be Replaced"
)
)
-
CALCULATE(
SUM('YourStockTable'[Amount]),
FILTER(
ALL('YourDateTable'),
'YourDateTable'[Date] = CurrentDate
),
FILTER(
ALL('YourStatusTable'),
'YourStatusTable'[Status] = "Current"
)
)

This measure calculates the net change for each status category on the current date. It sums the amounts of stock for each status and subtracts the amount of stock in the "Current" status.

By calculating the net change separately, you avoid the circular dependency issue. You can then use this net change measure in your calculations or visualizations as needed.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.