Forum Discussion
Visualizing historical balance
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
10 Replies
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.
I would start with another date column in your data.
- 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.
- Relate that new date column to your Date column in your Date Table.
- 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.
- 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.
Hi,
That is a confusing question. What exact result do you want?
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 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
Hi,
Share the link from where i can download your PBI file.
