Calculate Date Difference in Power BI with multiple tables joined

0
votes

I'm trying to calculate the difference between 2 dates in Power Bi. I need to join 3 separate tables in order to get the correct data back. Here is my Dax(Measure) code

Average Decision Time = CALCULATE(
DATEDIFF(Enquiries_venues[RecordCreated],EnquiryStatusVersion[RecordCreated], DAY),
FILTER ( Enquiries, Enquiries[TestEnquiry] = 0 && (Enquiries[EnquiryStatusId] = 45 || Enquiries[EnquiryStatusId] = 50 || Enquiries[EnquiryStatusId] = 55 || Enquiries[EnquiryStatusId] = 56 || Enquiries[EnquiryStatusId] = 60)),
FILTER ( Enquiries_Venues, Enquiries_Venues[EnquiryVenueProposalId] = 60),
FILTER ( EnquiryStatusVersion, EnquiryStatusVersion[EnquiryStatusId] = 45)

)

However the date-diff part is not able to recognize the tables at this point. When I put this section at the bottom it also doesn't work as the calculate function requires the expression to come first.

Here is the error message enter image description here

Any Help would be greatly appreciated.

2
powerbi

2 Answers

0
votes

You are passing table columns into DATEDIFF instead of scalar dates that it expects.

Try wrapping those columns in an aggregate function like MAX in order to get a single date value.

0
votes

Can you please try the following measure?

Average Decision Time = CALCULATE(
AVERAGE(DATEDIFF(Enquiries_venues[RecordCreated],EnquiryStatusVersion[RecordCreated], DAY)),
FILTER ( Enquiries, Enquiries[TestEnquiry] = 0 && (Enquiries[EnquiryStatusId] = 45 || Enquiries[EnquiryStatusId] = 50 || Enquiries[EnquiryStatusId] = 55 || Enquiries[EnquiryStatusId] = 56 || Enquiries[EnquiryStatusId] = 60)),
FILTER ( Enquiries_Venues, Enquiries_Venues[EnquiryVenueProposalId] = 60),
FILTER ( EnquiryStatusVersion, EnquiryStatusVersion[EnquiryStatusId] = 45)