Calculated column not returning expected results

[Anonymous]

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?

[Anonymous]

Sorry, just to clarify Service Cases and Invoice Details tables are named 'Invoice List' and 'Case List'

[Anonymous]

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

[Anonymous]

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.