Previous month ending balance as current Month Beginning Balance

Reply
Visitor

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. 


Accepted Solutions
Purple Belt

Re: Previous month ending balance as current Month Beginning Balance

Hi

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 Replies
Black Belt

Re: Previous month ending balance as current Month Beginning Balance

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.


______________________________________________________________________________________________
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
______________________________________________________________________________________________
Visitor

Re: Previous month ending balance as current Month Beginning Balance

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. 

 

DateAccount NameAmount AR Beginning balanceAR Difference 
Jan-19Unbilled Sales105162   
Jan-19AR Balance81963 AR Balance from December

Dec Balance-81963

Jan-19Deposit-6000   
Feb-19Unbilled Sales185423   
Feb-19AR Balance65974 8196381963 - 65974
Feb-19Deposit-4125   
Mar-19Unbilled Sales582133   
Mar-19AR Balance49652 6597465974 - 49652
Mar-19Deposit-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. 

 

DateAccount NameAmount
Jan-19Unbilled Sales105162
Jan-19AR Balance81963
Jan-19Deposit on Contract-6000
Jan-19AR DifferenceDEC balance -81963
Feb-19Unbilled Sales185423
Feb-19AR Balance65974
Feb-19Deposit on Contract-4125
Feb-19AR DIfference81963-65974
Mar-19Unbilled Sales582133
Mar-19AR Balance49652
Mar-19Deposit on Contract-7463
Mar-19AR Difference65974-49652

 

Hope that makes sense Smiley Happy

Major Blue Belt

Re: Previous month ending balance as current Month Beginning Balance

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 Smiley Happy

Black Belt

Re: Previous month ending balance as current Month Beginning Balance

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:3.png

 

4.png

 

5.png

 

6.png

 

7.png

 

 

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

1.png

 

2.png

 


______________________________________________________________________________________________
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
______________________________________________________________________________________________
Black Belt

Re: Previous month ending balance as current Month Beginning Balance

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

 


______________________________________________________________________________________________
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
______________________________________________________________________________________________
Purple Belt

Re: Previous month ending balance as current Month Beginning Balance

Hi

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.

Visitor

Re: Previous month ending balance as current Month Beginning Balance

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 Smiley Happy

Black Belt

Re: Previous month ending balance as current Month Beginning Balance

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.


______________________________________________________________________________________________
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
______________________________________________________________________________________________
Announcements
Looking for the latest Community solutions? Please visit our accepted solutions board here!