Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
hakimissimo
Regular Visitor

Comparing two dates from different tables

Hello,

 

Very beginner with PowerBI, i'm trying to compare two dates to determine some elapse time.

 

two tables : incidents and incident-sla

 

The tables have a relationship based on incident number (Number).

 


What i tryed with add a new column : Column = DATEDIFF(Incidents[Opened];'Incident-sla'[start time];DAY)

 

 

but i have the error : a single value for column 'start time' in table incident-sla cannot be determined....

 


Any help appreciated 🙂

 

 

++

 

 

 


 

 

2 ACCEPTED SOLUTIONS

upload to onedrive and include a link



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


View solution in original post

Hi @hakimissimo,

 

I have fixed your Calc for you and uploaded a new file to the share you created.

 

Steps:

- Change relationship direction from 'both' to 'single'

- updated the formula to the following, which returns the max('incident-sla'[Start time]) where the 'incident-sla'[Number] = 'Incidents'[Number].

 

 

BeforeAck = CALCULATE(max('incident-sla'[Start time]), FILTER('incident-sla', 'incident-sla'[Number] = 'Incidents'[Number]))

- Add a Table visual that show how the max value is returned for each rown in the header and detail tables

 



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


View solution in original post

11 REPLIES 11
vsslasd1
Helper III
Helper III

Hello, I have the same Issue. 

Trying to subtract one date from another. 

CRM Opportunity entity has one record, the secondary table has multiple records. 

Not having any luck, and am a little confused about the DAX Syntax. 

Thius doesn't work, probably because the new_fixuplists has more than one re

StartupDays = DATEDIFF(opportunities[new_bookeddate], new_fixuplists[new_startupmeetingdate],DAY)
hakimissimo
Regular Visitor

Hello,

 

Very beginner with PowerBI, i'm trying to compare two dates to determine some elapse time.

 

two tables : incidents and incident-sla

 

The tables have a relationship based on incident number (Number).

 

What i tryed with add a new column : Column = DATEDIFF(Incidents[Opened];'Incident-sla'[start time];DAY)

 

but i have the error : a single value for column 'start time' in table incident-sla cannot be determined....

 

Any help appreciated 🙂

 

++

 

 

 

 

Hi @hakimissimo

 

In calculated column, we should use function “Related” if the “Start time” in the 1-side of the relationship (1:1, 1:* or *:1). Please check it out and try this formula.

 

Column =
DATEDIFF ( Incidents[Opened]; RELATED ( 'Incident-sla'[start time] ); DAY )

  

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Dale,

 

Thank you for quick feedback, it's really appreciated.

 

I have modified my request as you suggested.

 

Now i have the error : 'incident-sla[Start time]' either does not exist or does'nt have the relationshipe to any table...."

 

I have cheched the relationship and there is a many to 1 (please see attached image)... any ideas? PowerBIerror.png

The related function relates the many side to the side, if you create your column in the incident-sla table your function will work something like

 

Column =
DATEDIFF ( 'Incident-sla'[start time], RELATED ( Incidents[Opened] ), DAY )

It does not really make sense to create the column on the one side on the relationship as you would not know which of the many [start time] values to use for the date diff to the [opened]. 

 

If you are trying to use the max or min value from the many side to get the last/first value on the one side, then you could use the lookupvalue function within a calculate function function.

 

Hope that helps

 



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


Hi Richbenmintz,

 

First thank you for feedback.

 

What i'm trying to do is basically to compare Start time with Oponed date for each incident (the moment where somebody is working on the resolution).

 

The tricky point is for a same incident 123456, there could be two start time dates (when ticket is going from support group 1 to support group2...)

Ideally i would like to retrieve the most recent date (because previous dates became obsolete).

 

I am not familiar with PowerBI and i do not know how to use lookupvalue function.

 

Hope to read you soon.

 

++

 

 

 

Hi @hakimissimo,

 

please provide a sample pbix file and I can help with the formula



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


Hi Richbenmintz,

 

How can i attach my pbix file to the post?? (very newbie 🙂

 

++

upload to onedrive and include a link



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


I have uploaded pbix file on my g drive : https://drive.google.com/drive/folders/0B74jBoLmjb1yTkktS2tONC1WM00

 

ask access please.

Hi @hakimissimo,

 

I have fixed your Calc for you and uploaded a new file to the share you created.

 

Steps:

- Change relationship direction from 'both' to 'single'

- updated the formula to the following, which returns the max('incident-sla'[Start time]) where the 'incident-sla'[Number] = 'Incidents'[Number].

 

 

BeforeAck = CALCULATE(max('incident-sla'[Start time]), FILTER('incident-sla', 'incident-sla'[Number] = 'Incidents'[Number]))

- Add a Table visual that show how the max value is returned for each rown in the header and detail tables

 



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.