Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |