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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Anonymous
Not applicable

Roll Report Measure for invoices at month end

Hello everyone -

I'm looking for assistance in helping me create a Roll Report measure for our Collections Team. Our company has N30 terms on our invoices, meaning the customer needs to pay off invoices within 30 days.  So for example, if you look at Invoice #1: the invoice was generated on 4/27/2021 and is due on 5/27/2021. Today is 5/21/2021, 6 days BEFORE the invoice is due, so this is reflected in "Days Outstanding" column with a -6. If you look at Invoice #5, the invoice due date was 5/13/2021 and today is 5/21/2021 so the invoice is 8 days past due (shown in the Days Oustanding column), and so forth. 

 

Power BI Example.jpg

 

My question is, I'm trying to create a measure which will show all invoices which will have a days outstanding of 1 or more on the LAST day of the month.  So using this month as an example - I want to see all invoices that will be past due on May 31st.  Using my chart above - Invoice 1,2,5,6 would show up in this measure because these (4) invoices would have a days outstanding of 1 or more on May 31st.  Invoice 3 and 4 would NOT show up in this measure because on May 31st, these invoices will still NOT be past their due date. 

 

I've tried creating an End Of Month column and working measures based off that, but I keep hitting a dead end.  Thanks in advance for your assistance!  

2 REPLIES 2
k_anupindi
Regular Visitor

Hello,
Were you able to resolve the issue mentioned above? I’m currently working on something similar and would appreciate any advice on how you resolved it.
Thank you!

daxer-almighty
Solution Sage
Solution Sage

Here's a file that does it. Please bear in mind that the 'Universal Date/Time' dimension should not be connected to the 'Invoices' table. If you set up a relationship for any reason between these two tables, you'll have to make sure that the measures use USERELATIONSHIP to disconnect them for the duration of the calculation. Otherwise, everything will be screwed up.

 

By the way, the 'Days Outstanding' column is totally redundant and in fact it makes the model rigid. Also, note that I used the [# Outstanding at EoM] (EoM = End of Month) measure to filter out the invoices in the table visual. This measure can be used to do this and also on its own, as I demonstrate in the file.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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