Visualizing historical balance

[Anonymous]

I am building visuals for providing a view of invoice balances based on a date selection. 

Based on selected date, the view will show the invoices and the amount owed by customer and a chart with total balances by month. 

Transactions are in the form of this below table. I have a calendar table from 2015.  

Current balances is  available from the balance column. I have the bar chart showing the current balalnces due for each month. But i would need to build something to look at the balance due as of any particular date.  What will be the approach without a performance issue. 

Customer	Invoice	Order Date	Amount	Balance	Paid Date	Paid Amount
5000	INV1	01/10/2019	1000	0	01/29/2019	1000
5010	INV5	01/20/2019	1500	0	02/12/2019	1500
5000	INV6	02/05/2019	2000	0	02/28/2019	2000
5015	INV7	04/02/2019	4200	4200
5016	INV7	04/10/2019	3700	3700

I have a calendar table with dates from 2015

[Anonymous]

Hi @Anonymous ,

Please check the following steps.

1# Create a yearmonth column for your CALENDAR table.

yearmonth = FORMAT('CALENDAR'[Date],"YYYYMM")

2# Create a measure as below.

Measure = CALCULATE(SUM('Table'[Amount]),FILTER(ALLEXCEPT('Table','Table'[Invoice]),FORMAT('Table'[Order Date],"YYYYMM")=SELECTEDVALUE('CALENDAR'[yearmonth])||FORMAT('Table'[Paid Date],"YYYYMM")=SELECTEDVALUE('CALENDAR'[yearmonth])))

 Result would be shown as below.

If i misunderstand your meaning, please show the expected result to me.

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[Anonymous]

@Anonymous @Ashish_Mathur 

Sorry for the delay in my reply. 

The ask is to show the Due amount details based on a date .  

From the above data, if the date selected is 11/20/2017, only the first transacation should be displayed as the $1600 is due as of 11/20/2017 and the rest of the orders belongs to 2019 or they are all greater than 11/202/2017

[Ashish_Mathur]

Hi,

Has your question been answered?

[Anonymous]

Hi @Anonymous ,

Please chekc the measure below.

Measure = 
var amount = CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Order Date]<=SELECTEDVALUE('CALENDAR'[Date])))
var paid = CALCULATE(SUM('Table'[Paid Amount]),FILTER('Table','Table'[Paid Date]<=SELECTEDVALUE('CALENDAR'[Date])))
return
amount-paid

Result would be shown as below.

 

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[Anonymous]

@Ashish_Mathur 

Here is the link - https://drive.google.com/open?id=1wcZYs16fUk-RROxgmro02P7CFGgJeo_9

[Anonymous · Accepted solution]

Hi @Anonymous ,

Did my formula work for you? Let me know if i miss something.

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[Ashish_Mathur]

Hi,

That is a confusing question.  What exact result do you want?

[edhans]

I would start with another date column in your data.

1. Create a date column that is the "Latest Activity" which is if there is a payment, use the Payment date, otherwise use the Order Date.
2. Relate that new date column to your Date column in your Date Table.
3. You need a measure that shows the balance, which is Amount - Paid Amount. Don't worry about Paid Amount being null. Number-BLANK=Number in DAX.
4. You can then create a table/matrix/whatever that uses the Date[Date] from your Date table then summarizes or pulls whatever you want from this table below.

[Greg_Deckler]

I do not understand having a calendar of dates from 2015 when you data shows from 2019.

Unless I am seriously missing something, generally the way you handle what you have presented is to use a date slicer to zero in on particular dates.