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.
Hi Community
I have a PBI report with 2 main tables. Service Cases and Invoice Details.
On the Service Case table I have a column that identifies for each record the date of that customers next order AFTER the date the case was raised. The DAX i am using is as follows;
Next Order = IF('Case List'[Re Ordered],CALCULATE(min('Invoice List'[Invoice Date]),FILTER('Invoice List','Invoice List'[Customer]='Case List'[Customer] && 'Invoice List'[Invoice Date]> 'Case List'[Created On])),0)
[Re Ordered] is a boolean field that indicates if the customer for that record has subsequently re ordered. It uses similar DAX;
COUNTX(FILTER('Invoice List','Invoice List'[Customer]='Case List'[Customer] && 'Invoice List'[Invoice Date]> 'Case List'[Created On]),'Invoice List'[Name]) <> 0
I have used min('Invoice List'[Invoice Date]) so i get the first [Invoice Date] in Invoice List for that [Customer] after the [Created On] date.
'Case List'[Customer] and 'Invoice List'[Customer] are hexdecimal ID strings
'Invoice List'[Invoice Date] and 'Case List'[Created On] are both formated as dd/mm/yy hh:mm:ss.
Mostly this is working correctly but there are several records where it is not returning any value.
If i apply the same filtering logic manually to 'Invoice List' for each of the records retunring no value, i have a valid record to return the date on.
What am i missing / doing wrong?
Sorry, just to clarify Service Cases and Invoice Details tables are named 'Invoice List' and 'Case List'
Can you post the pbix file? Onedrive/dropbox works. It's hard, at least for me, to go through your formula without any formatting. thx
Hi Nick,
Thanks for taking the time to respond.
I fear that is not possible. There is a lot of sensitive data in there not to mention the connection data to our Dynamics 365 CRM.
Copying the tables and stripping out the sensitive data and creating a copy pbix would be a long job.
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 |
---|---|
104 | |
96 | |
80 | |
67 | |
62 |
User | Count |
---|---|
137 | |
106 | |
104 | |
81 | |
63 |