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

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
Icon for Super User rankSuper 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
    Icon for Super User rankSuper 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