Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
Hi @Pbi07 ,
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.
Hi @Pbi07 ,
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.
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,
Has your question been answered?
Hi @Pbi07 ,
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.
Hi,
Share the link from where i can download your PBI file.
Here is the link - https://drive.google.com/open?id=1wcZYs16fUk-RROxgmro02P7CFGgJeo_9
Hi @Pbi07 ,
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.
Hi,
That is a confusing question. What exact result do you want?
I would start with another date column in your data.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingI 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.
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |