Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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.
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!
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!
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
21 | |
15 | |
15 | |
11 | |
7 |
User | Count |
---|---|
25 | |
24 | |
12 | |
12 | |
12 |