Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Here is my situation:
I have a dataset of invoices and cash receipts. Each invoice has a single due date, each cash receipt has a cash received date, and each invoice has an invoice date. I have created a measure that dynamically calculates the days past due based on the calendar (from a date table) dates shown and an invoice due date.
Here is what the data looks like:
Invoice | Date | Payment Type | Payment | Due Date | ||||
1A1 | 1/1/2020 |
| Invoice | 1000 | 1/3/2020 | |||
1A1 | 1/4/2020 | Cash Received | -500 | 1/3/2020 | ||||
1A1 | 1/5/2020 | Cash Received | -500 | 1/3/2020 | ||||
1B1 | 2/4/2020 | Invoice | 2000 | 2/7/2020 | ||||
1B1 | 2/5/2020 | Cash Received | -1500 | 2/7/2020 | ||||
1B1 | 2/20/2020 | Cash Received | -250 | 2/7/2020 | ||||
1B1 | 2/21/2020 | Cash Received | -200 | 2/7/2020 | ||||
1B1 | 3/1/2020 | Cash Received | -50 | 2/7/2020 |
I have a date table connected to the Date Column as an active relationship and another connection based on the due date that is inactive.
Note, the screenshots below are the calculations used on the actual data while the table above is dummy data. One can substitute [Due Date] for invoice due date.
Here are the calculations that I use for the dynamic date difference:
Then I run that over all dates in this calculation:
This calculation works great when only 1 invoice is chosen. The problem is when I select more than 1 invoice, it is still using the due date from the first invoice. I know the problem stems from the Dynamic Aging Days - Temp calculation, specifically the MIN('Historical Aging Dataset' [Invoice Due Date]). What I do not know how to do is to use DAX to force Power BI to use due dates for each invoice rather than the earlier due date selected from a set of invoices.
I have tried using these for the due date calculation but none have worked.
Any ideas on what to do would be very appreciated. This is literally the last thing I need for this aging data power bi report.
Hi, @sjrrkb123
As mentioned by @lbendlin ,try measure as below:
Calculate(MIN('Historical Aging Dataset' [Invoice Due Date]), ALLExcept('Historical Aging Dataset','Historical Aging Dataset'[Invoice])
If the function does not work, please share your pbix file and expected results for testing.
Best Regards,
Community Support Team _ Eason
Unfortunately, this did not work. It still works great for a single invoice but getting more than 1 does not yield the correct results.
For example, using invoice 1A1 (from fake data), there are 3 events
and when only selected, the aging days calculation looks as follows:
However, when looking at it through the lens of two invoices, 1A1 & 1B1, then things break down.
Here is the information for both invoices:
Unfortunately, the dynamic aging days calculation does not take this into account as can be seen below ere there are no Current Aging values on 2/4/2020 and after.
As it stands now, I am using the following calculations for aging days and then for bucketing them in the appropriate aging group;
Dynamic Aging Days - Temp:
Here is the Power BI link:
https://drive.google.com/file/d/1-UoU12e7n48ceZAEG88nmw8T_3SLsjsJ/view?usp=sharing
Use ALLEXCEPT(invoicenumber)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
95 | |
83 | |
70 | |
66 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |