Previous month ending balance as current Month Beginning Balance

Hi I am trying to create a beastmode that takes this months balance and subtract it from last months balance also known as this months beginning balance.

sum(CASE WHEN `Month ` = '3' then `AR Balance` else 0 end) - sum(case when `Month ` = 4 then `AR Balance`else 0 end)

My problem with the above is the two values aren't ending up in the bar graph together under the current month. Instead I get a postive number for Month 3 and a negative number for Month 4 and I'm not sure how to make the appear in the same bar.

Basically I need the change in the balance from last month to this month all as the value for this month.

Find more posts tagged with

Accepted answers

rado98

Using Magic ETL you could create a column with the balances offset by month.

Filter in only the AR Balances

Use the Rank and Window tile to create a column with the amounts offset by one month

Use Beastmode to get the difference

Apologies for not being able to be a detailed as I would want to.

All comments

ST_Superman

Could you share a screenshot and a sample of your data set? Also, it would be helpful if you showed an idea of what you are wanting as an output.

user00003

For example if I had the below data on the left I want the columns on the right. It has the AR beginning balance for February as the AR blanace that was in January.

Date	Account Name	Amount	AR Beginning balance	AR Difference
Jan-19	Unbilled Sales	105162
Jan-19	AR Balance	81963	AR Balance from December	Dec Balance-81963
Jan-19	Deposit	-6000
Feb-19	Unbilled Sales	185423
Feb-19	AR Balance	65974	81963	81963 - 65974
Feb-19	Deposit	-4125
Mar-19	Unbilled Sales	582133
Mar-19	AR Balance	49652	65974	65974 - 49652
Mar-19	Deposit	-7463

If I use the formula I would like to get the difference between these two numbers for February. This would show how the account changed within the month.

Febraury beginnning balance - Febraury Balance. = February AR Difference

I think my main problem is getting the January balance to show as the February beginning balance and then subtracting the two so there is a knew number.

Or I could make a new row in the data on the left with an account name that says "AR Change". This would have the difference between February and January.

Also haven't been able to figure out how to pull the previous month number and subtract the current month number from that to get a new account.

Date	Account Name	Amount
Jan-19	Unbilled Sales	105162
Jan-19	AR Balance	81963
Jan-19	Deposit on Contract	-6000
Jan-19	AR Difference	DEC balance -81963
Feb-19	Unbilled Sales	185423
Feb-19	AR Balance	65974
Feb-19	Deposit on Contract	-4125
Feb-19	AR DIfference	81963-65974
Mar-19	Unbilled Sales	582133
Mar-19	AR Balance	49652
Mar-19	Deposit on Contract	-7463
Mar-19	AR Difference	65974-49652

Hope that makes sense ?

Sweep_The_Leg

might need an extra case statement?

case when month = '2' then ((case when month = 2 then 'amount' else 0 end)-(case when month = '1' then 'amount else 0 end)) else 0 end

let me know ?

ST_Superman

A case statement won't do the trick here. The basic reason is that when evaluating a case statement, you can only look at one row at a time. This means that you need your values for the previous month to be in the same row as your other values. You can do this via an ETL data flow with a few transforms:

You can then create a flex table or regular table card to show the changes (or combo in a story)

ST_Superman

Actually, the more I thought about it, if the flex table is sufficient, you can create it without the ETL at all:

rado98

Using Magic ETL you could create a column with the balances offset by month.

Filter in only the AR Balances

Use the Rank and Window tile to create a column with the amounts offset by one month

Use Beastmode to get the difference

Apologies for not being able to be a detailed as I would want to.

user00003

The flex table in Analyzer works but...the next part of this is to subtract out another account to get another value.

I tried the ETL but there must be way too much data because it ran for 20 hours before I canceled it.

So I'm not sure where to go from here.

In the end I need (AR beginning balance - AR Ending balance) - Estimated Billings.

If i can do this with the Flex table in Analyzer that would work but it doesn't appear that way.

Thanks for all your help though it is really appreciated ?

ST_Superman

You will need to do this through an ETL or MySQL data flow.

If the ETL is taking that long, then there is probably something wrong with your settings.

If you have any Domo consulting hours, you may want to engage that team to take a look at your ETL and see if they can find the issue.

If you are comfortable posting the settings for your ETL here, then I could take a look as well.

Another option would be for you to post the schema for your data set (and maybe some sample data) in an excel sheet so that I could create the transforms in a MySQL dataflow which you could then just copy and paste the code into your instance.

I would need the full data set schema though, not just the few columns that were listed in the post.