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_OPENED]))
RETURN
    GENERATE (
        BASECALENDAR,
        VAR BaseDate = [Date]
        VAR YearDate = YEAR ( BaseDate )
        VAR MonthNumber = MONTH ( BaseDate )
        RETURN ROW (
            "Day", BaseDate,
            "Year", FORMAT(BaseDate,"yyyy"),
            "Month Number", MonthNumber,
            "Month", FORMAT (BaseDate, "mmmm"),
            "Month Year", FORMAT (BaseDate, "yyyymm"),
            "DayOfMonth", DAY(BaseDate)
        )
    )
 
I want to use the date in this table, and subtract it from the Date_Opened column in my table called "Query"
Is there a way to do this?
 
Thank you!
Sarah
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

5 Replies

    • 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