Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
echolima201
Frequent Visitor

I need to create a chart showing the value of open invoices for each month. For this I have a table

 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..

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Open invoices by month, day, quarter... Same formula works.

 

File attached.

 

Best

D

View solution in original post

10 REPLIES 10
Ashish_Mathur
Super User
Super User

Hi,

Share a dataset and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
TomMartens
Super User
Super User

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

Open invoices by month, day, quarter... Same formula works.

 

File attached.

 

Best

D

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! 

Anonymous
Not applicable

It works correctly for correct data. No tweaking needed.

Best
D

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? 

 

Open Misc Invoice - no check date.png

Anonymous
Not applicable

Do not leave blanks in your fields. This always creates a lot of issues, slows down code and makes code more complex. Put a date far into the future in there and adjust the calendar to show the right entries for such invoices (e.g., "no end date" or "still open"). Then it'll work fine. My model does not allow for BLANKS.

Best
D
Anonymous
Not applicable

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.