Previous Month balance based on Closed Date

[KW123]

Hi, 

I have a list of customer ID's with closed accounts.  The data is organized by report month and I have columns with the customer ID, their account number, the account open date, the account closed date, and the current account balance (which is $0 because the account is closed) 

I need to find out what the account balance was the month prior to the account closing.  I am having a hard time finding the previous month that each customer closed their account because each row is a different date.  Additionally, the current balance is attached to the report month.  So if I filter the entire report by the report month, it will show the balances for that filtered month, not necessarily the closed month which is what I am looking for. 

Lets say the report is filtered by report month November.  This means that all current balances are 0.  
What I am looking to get

	Account Number	Open Date	Closed   Date	Current balance (Based on report Month)	What I am looking to get: Previous Closed Month	What I am looking to get: Previous Closed Month Balance
1	555	02/03/2020	02/10/2022	$0.00	01/10/2022	xxx
2	556	10/09/2019	05/21/2021	$0.00	04/21/2021	xxx
3	557	03/20/2009	06/12/2020	$0.00	05/12/2020	xxx
4	558	03/25/2019	05/08/2022	$0.00	04/08/2022	xxx
5	559	12/18/2020	08/09/2021	$0.00	07/09/2021	xxx
6	560	04/21/1995	06/04/2000	$0.00	05/04/2000	xxx
7	561	12/05/2001	07/08/2022	$0.00	06/08/2022	xxx
8	562	10/19/2020	02/09/2021	$0.00	01/09/2021	xxx
9	563	01/10/2000	03/04/2022	$0.00	02/04/2022	xxx

[amitchandak]

@KW123 , Join both the dates with a date of a date table, Assume close date have inactive join

You need a measure like

calculate( calculate( SUM(Table[Amount]),USERELATIONSHIP ('Sales'[CreateDate], 'Date'[Date])),DATESMTD(dateadd('Date'[Date],-1,month)) )

calculate( calculate( SUM(Table[Amount]),USERELATIONSHIP ('Sales'[CreateDate], 'Date'[Date])),previousmonth('Date'[Date]))

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

Also check

openingbalancemonth, openingbalancequarter, openingbalanceyear: https://youtu.be/6lzYOXI5wfo
Power BI Allocating Targets- closingbalancemonth, closingbalancequarter, closingbalanceyear:https://youtu.be/yPQ9UV37LOU

[KW123]

@amitchandak Any suggestions? I am very stuck!

[Anonymous]

Hi @KW123 ,

If you want to get the Current balance for the month before the closed date, you can create a measure like the following shows:

last value = MAXX(FILTER(ALL('table'),eomonth('Table'[closed date],0)=EOMONTH(max('Table'[closed date]),-1)),'Table'[current value])

Then you can get the current value corresponding to the month on the closing date,Attached is a picture for your reference for comparison.

You can also refer to my pbix file for better understanding.

Best regards,

Community Support Team Selina zhu

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

[KW123]

@Anonymous 

Thank you for taking the time to reply! I tired the measure but it took a very long time to load and eventually there just wasn't enough memory to complete it. 

Looking at your example though, I am not sure that this is what I will need.  If we assume that the Sum of Index is a customer ID, it looks as though the DAX will return the previous value of a different customer ID.  

What I need to do is return the last value of the same customer ID.  In the Data set up screenshot I took, that is for one customer ID, not multiple.  In my report, I will have all customer ID. I hope that makes sense. 

[Anonymous]

Hi @KW123 ，

Can you show me the desired result you need, preferably in the form of a table expressing what you need to achieve, as my screenshot shows?

Best regards,

Community Support Team Selina zhu

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

[KW123]

Hi @amitchandak 

Thanks so much for your reply.  I used the first DAX measure:

Measure 2 = calculate( calculate( SUM(Query1[CurrentBalance]),USERELATIONSHIP ('Query1'[ClosedDate], 'Dates'[Date])),DATESMTD(dateadd('Dates'[Date],-1,month)) )

And my column is giving me $0 for anyone with a closed date in October.  For everyone else, it's giving me blanks. 

I think the reason why is that the current balance is based on the report month which is currently set to November.  What I need to do is get the Current balance for the month before the closed date. 

Here is how the data is set up: