Date Different Between Two Tables

[Anonymous]

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

[amitchandak]

This can be done like this

datediff(MIN(Query1[DATE_OPENED]),MAX(Query1[DATE_OPENED]),day)

Row context is important, please check my blog for that. you might have to use summarize or values for correct grand total

https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Differ...

[Anonymous]

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]

@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]

@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 · Accepted solution]

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