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))
      • amitchandak's avatar
        amitchandak
        Super User
        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