Forum Discussion

hakimissimo's avatar
hakimissimo
Regular Visitor
9 years ago
Solved

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 :-)

 

 

++

 

 

 


 

 

Need Help
  • richbenmintz's avatar
    richbenmintz
    9 years ago

    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

     

11 Replies

  • hakimissimo's avatar
    hakimissimo
    Regular Visitor
    9 years ago

    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 :-)

     

    ++

     

     

     

     

    • v-jiascu-msft's avatar
      v-jiascu-msft
      Microsoft Employee
      9 years ago

      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

      • hakimissimo's avatar
        hakimissimo
        Regular Visitor
        9 years ago

        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? 

  • vsslasd1's avatar
    vsslasd1
    Helper III
    6 years ago

    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)