Total previous year used like current total

[giuliapiazza94]

Hi guys,

I've 3 tables: A,B,C

A

B

C

Result:

the first colum (may 2023) is simple, the others are the problem.

I need to see the difference (1000€ - 370€ = 630€) in the row "liquid assets" of next month (june 2023)

The difference of june (= 430 €) --> liquid assets of july 2023

The difference of july (= 430€) --> liquid assets of august

....the same for the next months

Have you idea to do this?

Thank you 🙂 

[123abc · Accepted solution]

Create a Calculated Column for Differences: Create a new calculated column in your data table to calculate the differences between consecutive months. You can use a formula like this:

Difference =
CALCULATE(
SUM(A[Amount]) - SUM(B[Amount]),
FILTER(
ALL('Date'[Month]),
'Date'[Month] = EARLIER('Date'[Month]) + 1
)
)

1. Adjust this formula based on your actual column names and structure.

2. Create a Running Total Measure: Now, create a measure for the running total. This measure should accumulate the differences for each month. Here's an example:

RunningTotal =
CALCULATE(
SUM('YourTable'[Difference]),
FILTER(
ALL('Date'[Month]),
'Date'[Month] <= MAX('Date'[Month])
)
)

1. Again, adjust the table and column names accordingly.

2. Visualize the Data: Finally, use a line chart or any suitable visualization to display the running total over time. Put the Date field on the x-axis and the RunningTotal measure on the y-axis.

These steps should help you create a Power BI report that shows the running total of differences in "liquid assets" over time. Adjust the formulas based on your actual data model and requirements.