How to calculate bank balance per week

[QwertyMartijn]

Hey guys,

I've been struggeling with this for a while now. I want to calculate the bank balance (composed of 3 bank accounts) per week.

Right now i tried calculating the sum, but that doesn't work; there is a closing value per day in the data, so if i sum it up, it will sum up all the closing values. I only need the total of the 3 accounts for the latest date.

This is what i've tried. The first two parts in calculating the debitors en creditors work. But i need some help on the final part of calculating the bank balance.

Thanks in advance!

[QwertyMartijn]

@MohammadLoran25 UPDATE:

I have created this measure, and it displays the correct balance for the current week.

Now, how do i transform this into the IF statement to show it for each week?

Somehow if i place it into the IF statement like this:

The 'bank' row just dissapears from the matrix.

[MohammadLoran25]

I made a change in my response. Check it again please.

@QwertyMartijn 

[MohammadLoran25 · Accepted solution]

@QwertyMartijn ,

In your Final Table create a Calculated Column:

WeekDay=WEEKDAY('Final'[Datum])

Then Create the measure below:

BankBalance =
SUMX (
    FILTER (
        'Final',
        'Final'[WeekDay] = 7
            || 'Final'[Datum] = CALCULATE ( MAX ( Final[Datum] ), ALL ( Final ) )
    ),
    'Final'[Saldo eind]
)

 If I understand correctly, It is what you need. To work as expected, you need to put Week Column in Matrix as well. Then it would show the balance per week. If you want **bleep** in another format of visualization, let me know.

[QwertyMartijn]

Hi,

It now displays the same amout in every column.

[MohammadLoran25]

It is not shown per week because you have not put the week column in the visual to separate.

I don't understand exactly how do you want to visualize it. But if you want it per week, you should put the week column in your matrix as well.

What is your exact need?

[QwertyMartijn]

My bad, i had put in the week of another table. Now the bank balance is correct. However, it is currently week 13 and my table is displaying week 14 (so its one month ahead). How do i change this in power query?

Also after week 19 it jumps back to week 2. (and after week 29 it jumps back to 3)

[MohammadLoran25]

You're Welcome @QwertyMartijn ,

Happy that I could help you.

I do not know your columns for debitor and creditor very well, but I think you need to calculate debitor and creditor totals weekly.
So you need to change your first 2 parts as below:

CALCULATE (
    SUM ( Final[Saldo_Crediteuren] ),
    FILTER ( FINAL, FINAL[WeekNo] = WEEKNUM ( MAX ( 'Date Table'[Date] ) ) )
)

[MohammadLoran25]

Instead of power query it is easier if you go to your date table and create a calculated column:

WeekNo = WEEKNUM('Final'[Datum])

Then put this column in your Matrix visual. Does it fix your problem?

[QwertyMartijn]

That did fix it!

I only have one last thing. Can't thank you enough already, been working on this for way too long.

I pasted the part for the bank balance in my original cashflow measure.

However, the creditors en debitors do not variate per week:

[MohammadLoran25]

@QwertyMartijn 

Create it as a measure

Not a calculated column.

If you put the measure and Week in a table visual, it should work.

[QwertyMartijn]

Somehow it wont slice on week. Maybe this total over all the weeks is correct, but it doesnt show up per week.

[QwertyMartijn]

@MohammadLoran25 Thanks for all help already. But i accidentally clicked on 'accept as solution'.

Sorry if i was unclear. It should be sliced by week, as shown in the picture i posted. The measure that you suggested now sums up all the values of the 7th day of the week (which i think might be correct), but it is not sliced by week yet.

[MohammadLoran25]

I made an update in my message.

What do you mean by sliced per week?

You need to put the week column in your matrix as well. How do you want to show it per week?

[MohammadLoran25]

Okay

But how do you want it to be shown per week? By date slicer?

Or putting Week in the matrix?

Please clear this too.

Thanks.

[MohammadLoran25]

Hi @QwertyMartijn ,

Try this:

TotalBalance =
CALCULATE (
    SUM ( Final[Saldo eind] ),
    FILTER ( ALL ( Final[Datum] ), Final[Datum] <= MAX ( 'Date Table'[Date] ) )
)

If this answer solves your problem, please give it a thumbs up and mark it as an accepted solution so the others would find what they need easier.

Regards,
Loran

[QwertyMartijn]

Hi Hohamad,

Thanks for your reply. Somehow my 'bank' does not show up in the table anymore.

[MohammadLoran25]

Would you please share a sample data with desired result as well?

[QwertyMartijn]

This is what the data looks like:

There are multiple bank accounts (Omschrijving) and each have a starting amount (bedrag) and a closing amount for that date (saldo eind). The goal is to calculate the total of the different bank accounts for each week.

In my original code, the filters i used do filter per week, but the function SUM is wrong, because it just sums up al the closing amounts.