cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
knotpc Helper I
Helper I

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

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. 

Capture.PNG

 

My PowerBi combined table looks like this. 

 

PowerBiTable.PNG

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

 

Clint

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Anonymous
Not applicable

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

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

View solution in original post

2 REPLIES 2
Highlighted
Anonymous
Not applicable

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

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

View solution in original post

knotpc Helper I
Helper I

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

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. 

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors