Using a measure to calculate DAYS between TWO DATES in TWO Tables Direct Query Mode.

[knotpc]

Hello, 

I am trying to measure OnTimeDelivery using PowerBI in Direct Query Mode connected to Syspro. The date fields are in multiple tables with a One to Many relationships linked to SalesOrder. Below are the basics of the tables fields. 

SorMaster[SalesOrder]

SorMaster[OrderDate]

ArSalesMove[SalesOrder]

ArSalesMove[ShipDate]

The measure code looks like this. 

Days-Ship = DATEDIFF(SorMaster[OrderDate],ArSalesMove[TrnDate],DAY) 

The error is this. 

My PowerBi combined table looks like this. 

Any ideas on what I am doing wrong? I am new to this. 

Clint

[Anonymous · Accepted solution]

Hey @knotpc

you need to provide a single value for the OrderDate or else the measure is not going to know which instance of the OrderDate to use. To get a round this, you can specify FIRSTDATE(SorMaster[OrderDate]) or maybe LASTDATE(SorMaster[OrderDate]). This will filter the OrderDate down to a single value that the measure can digest.

Hope this helps,

Parker

[knotpc]

Parker, 

It took me a bit of Googling, to figure what you proposed as I am a NOOB. But, it worked once I did the following. 

New Measure

Days-Ship = DATEDIFF(
FIRSTDATE(SorMaster[OrderDate]),
FIRSTDATE(ArSalesMove[TrnDate]),
DAY)

Thanks for your help.