topic Re: Calculate opening balance together with transaction based data into one visual in DAX Commands and Tips

Calculate opening balance together with transaction based data into one visual

Anonymous — Thu, 09 Jul 2020 05:58:51 GMT

Hello.

I hope you can help me out

I have to show both open and closing balances based on a lot of transactions from one dataset.

I have to show opening balance up top and closing balance in the buttom of my matrix visual and in between is the kind of transaction which have adjusted the opening balance for the selected month.

My data comes in the following diminsions:

Date, amount, Transaction group.

My problems is, how do i get the opening balance to show closing balance of last month for this month.

Currently it is look like this:

(Sorry for the red markings but the groups a confidencial)

But i need it to go like this:

I have gotten the first opening balance by creating a transaction group called opening balance and manuelle putting into my dataset.

This is not a year to date visual. I have to see the impact each transaction group have on the "current" opening balance pr. month.

Looking forward to hear from you.

Re: Calculate opening balance together with transaction based data into one visual

lbendlin — Thu, 09 Jul 2020 22:58:43 GMT

are 1,2,3,4 month numbers that are tied into your dates table?

Re: Calculate opening balance together with transaction based data into one visual

Anonymous — Fri, 10 Jul 2020 05:41:45 GMT

Yes that is correct

Re: Calculate opening balance together with transaction based data into one visual

lbendlin — Fri, 10 Jul 2020 09:51:15 GMT

- You don't need to worry about the closing balance, that's basically the total for the row (you can rename the "Total" label)

- You need a measure for the opening balance for each month - simple to implement

- BUT if you want your table to look like that you will also need to create measures for every group. Are you sure you want that?

One alternative would be to use two table visuals, one just for the opening balance and another for the actual data. You can make them look like they are one table.

Re: Calculate opening balance together with transaction based data into one visual

Anonymous — Fri, 10 Jul 2020 09:58:12 GMT

I dont want to create a measure for every group as it will confuse the user when drilling down for each category. (i assume you would create a measure for each transaction type and format the values to be on rows instead of columns)

I like the your alternative, but how is the closing balance calulated as it does not have the opening balance´? (a third table below the actual data table)

Re: Calculate opening balance together with transaction based data into one visual

lbendlin — Fri, 10 Jul 2020 10:02:05 GMT

There's no need for that if your total portion of the measure in the second table is cumulative. Actually in both tables, just shifted by a month.

Re: Calculate opening balance together with transaction based data into one visual

Anonymous — Fri, 10 Jul 2020 10:04:19 GMT

Would it be possible to show a eksamble, i dont quite get how you will calculate the closing balance without a opening balance (will you create a measure for each cateogry in this eksamble?

Re: Calculate opening balance together with transaction based data into one visual

lbendlin — Fri, 10 Jul 2020 10:27:36 GMT

This works for me:

cumul = var m=max('Table (2)'[Month]) var c = CALCULATE(sum('Table (2)'[Value]),ALLSELECTED('Table'),'Table (2)'[Month]<=m) return if (HASONEVALUE('Table (2)'[Group]) ,sum('Table (2)'[Value]) ,c)

If you want you can add the initial opening balance to c. Obviously that has to come from an outside data source.

Here's the sample data. First table is regular, second uses the cumulative measure.

Re: Calculate opening balance together with transaction based data into one visual

Anonymous — Mon, 20 Jul 2020 11:51:35 GMT

So i have finally found a suitable solution where my opening balance is the first value of my each month.

What is did was to dublicate my query, group all my rows into months. This will act as my opening balance for the following month. Then i "pushed" the date 1 month so it would look like my grouped values belonged to current month + 1. I then added this query to my original query and gave them "opening balance" as transaction group.

So now my data set has a opening balance value as part of the data.

Now my visual looks like this with a simple sum formual: