cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
knotpc Regular Visitor
Regular Visitor

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
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
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 Regular Visitor
Regular Visitor

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
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 60 members 1,070 guests
Please welcome our newest community members: