Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
i need to create a chart showing the value of open invoices for each month. For this I have a table of invoices with the book date (when we recorded the invoice), the paid date (when the invoice has been been paid) and the total value of the invoice.
The monthly value of open invoices is equal to sum of all invoices open before the end of the month but not paid (no paid date or paid date after the end of the month)
How could I generate the monthly total value based on these information?
Thanks a lot in advance for your help..
Solved! Go to Solution.
Open invoices by month, day, quarter... Same formula works.
File attached.
Best
D
Hi,
Share a dataset and show the expected result.
Hey @echolima201 ,
the challenge you are facing has a name: events-in-progress
This article https://blog.gbrueckl.at/events-in-progress/ provides deep insights on how to tackle this. The article also links to all relevant blogs and articles out there.
If it's not enough, please create a pbix that contains sample data, upload the pbix to onedrive or dropbox, and share the link. Don't forget to describe your expected result, based on the sample data.
Regards,
Tom
Hello,
How would you tweak this to include currently open invoices? It doesn't seem to be including those in the measure when I run it on my data. Thanks!
Thats not a very helpful response... Open invoices don't have an end date/closed date/check date. Your measure seems to ignore those. I'm looking for a something to show all of the items in red as well but it's only picking up the items in yellow. Any ideas?
It's best to not leave blanks in the date fields and handle this properly in the Calendar... But if you want, here's the code that does take into account invoices without end dates:
# Open Invoices =
// For this to work Calendar must be disconnected
// from the fact table.
var __lastVisibleDate = MAX( 'Calendar'[Date] )
var __result =
CALCULATE(
COUNTROWS( Invoices ),
// open before or on the end of the period
// and not paid or paid only
// after the end of the period.
// You can remove KEEPFILTERS in both conditions
// only when you are not going to slice by
// the fields BookDate and PaidDate. If you've
// hidden the columns from the user's view, then
// you're NOT going to slice and hence you CAN remove
// the modifiers.
KEEPFILTERS( Invoices[BookDate] <= __lastVisibleDate ),
KEEPFILTERS(
OR(
Invoices[PaidDate] > __lastVisibleDate,
ISBLANK( Invoices[PaidDate] )
)
)
)
return
__result
Best
D
How would you tweak this to use two different values? I used SUM instead of CountRows and used the net invoice amount to see what was due. It works great for invoices that are fully paid however, if the invoice is partially paid then it has a paid date from our system so it shows as if it is fully paid the current measure. Ideally, if i filter for a date prior to the paid date it should use the Net due amount, if i filter for a date after the paid date it should use another column for Open Amount which would be $0 for fully paid items. Thanks!
That worked. Thank you very much!
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |