Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Date Different Between Two Tables

Hi,   I was wondering how to find the number of days between tables?   My calendar table is as follows: Calendar = VAR BaseCalendar =     CALENDAR(MIN(Query1[DATE_OPENED]),MAX(Query1[DATE_OP...
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • amitchandak's avatar
    amitchandak
    6 years ago

    Or try a measure like

    Measure 3 = CALCULATE(sumx(SUMMARIZE(filter(crossjoin('Date',Sheet1),Sheet1[DATE_OPENED]<='Date'[Date] && Sheet1[REPAIR_DATE]>='Date'[Date]),[Date],[DATE_OPENED],Sheet1[WR_NO],"_diff",DATEDIFF([DATE_OPENED],[Date],DAY)),[_diff]),CROSSFILTER('Date'[Date],Sheet1[DATE_OPENED],None))

    Attachment after signature

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi amitchandak  again 🙂


    so I used this formula:

    ??Date Diff? = datediff(max('Calendar'[Date]),Query1[DATE_OPENED],day)
     
    This gives the date difference between the Date_Opened and the Max of the Calendar date I created, but how do I do this for any selected Date in the Calendar date?
     
    So if I wanted to use 4/10/2020, all Date_Openeds with the date of 4/10/2020 would have the ??Date Diff? = 0,
    but if the Date Opened date was 4/05/2020 (lets say there are 3 dates of 4/05/2020), the ??Date Diff? would equal 3.
    And this would change depending on the date that I choose in the Calendar Date table
     
    Please let me know if this does not make sense...

    Thank you!
    Sarah
     
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      amitchandak another way of asking the question is

       

      for each date in the Calendar table, I want to subtract these dates inidividually from all the Date Opened dates in the Query table

       

      I have been thinking about this and it seems like I would need to create a column for each date in the Calendar table?

       

      is this even possible?

       

      Thank you!
      Sarah

      • amitchandak's avatar
        amitchandak
        Super User
        6 years ago

        Anonymous , you can create a new table like and get from it

        Table = SUMMARIZE(filter(crossjoin('Date',Sheet1),Sheet1[DATE_OPENED]<='Date'[Date] && Sheet1[REPAIR_DATE]>='Date'[Date]),[Date],[DATE_OPENED],Sheet1[WR_NO],"_diff",DATEDIFF([DATE_OPENED],[Date],DAY))