Waterfall Charts - help needed please!

[JJ2022PBI]

Hello,

I would like to create a waterfall chart showing the movement in a bank account between two user defined dates. The categories used in the waterfall would be;

1) Opening Balance [the user selects a slicer that determines the date used for this]

2) Money in [I have an exisitng measure for this that will calculate the money coming in between the two dates]

3) Money out [I have an exisitng measure for this that will calculate the money coming out between the two dates]

4) Interest [I have an exisitng measure for this that will calculate the money coming out between the two dates]

5) Closing Balance [the user selects a slicer that determines the date used for this]

All the data for this is stored in a table called Assets with the below columns:

1) Date (linked to another date table)

2) Attribute (Opening balance, money in/out, interest and closing balance)

3) Version (Actual, Plan)

4) Value (£)

Thanks for your help!

James

[JJ2022PBI]

Thank you Dino Tao.

You data tables are similar to mine. I was hoping to do something slightly different. The end result would be something like this and instead of having 'Total' at the end it has the month which the suer has selected. Example in Excel below:

So the money in is the total money in between Oct-23 and Jul-24, same for money out and growth.

Thank you very much for your help.

[Anonymous]

Hi @JJ2022PBI ,

Sorry, the total in the default waterfall chart of Power BI cannot be modified. This is by design. However, the total itself shows the final result calculated based on the increase or decrease of the value and the initial calculated value (as long as your DAX is written correctly), which is the Closing Balance of the last day of the date selected by your slicer.

Best Regards,
Dino Tao

[Anonymous]

Hi @JJ2022PBI ,

You did not provide your data and measures, so I can only create sample data and measures for testing. If my sample data and measures are different from yours, please adjust them yourself or provide your sample data and DAX, thank you!
Here is my sample data:

And here are the measures:

Money In = 
CALCULATE(
    SUM(Assets[Value (£)]),
    Assets[Attribute] = "Money In"
)

Money Out = 
CALCULATE(
    SUM(Assets[Value (£)]),
    Assets[Attribute] = "Money Out"
)

Interest = 
CALCULATE(
    SUM(Assets[Value (£)]),
    Assets[Attribute] = "Interest"
)

Then I use this DAX to create another measure:

Closing Balance = 
 VAR Opening_Balance =  
 CALCULATE(
    SUM(Assets[Value (£)]),
    Assets[Attribute] = "Opening Balance",
    Assets[Date] = MIN('Date'[Date])
)
RETURN
Opening_Balance + [Money In] - [Money Out] + [Interest]

And create the waterfall chart:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.