Difference in dates for different meetings in DAX

[Elisa112]

I have created a matrix visual to show the difference in dates for various type of appointments.  The appointments are introduction/Assess/ stored in Meeting Type so the matrix looks like this:

UserID           Introduction     Assess                  DateDifferene

123000          12/01/2023      15/01/2023                        ?

15689             21/01/2023      24/01/2023                       ?

I am trying to find the date difference between Introduction and Assess. the matrix is set up as

Rows = userID

Columns = Type

Values = EarliestDate (Meeting[Date/time]).

I need to create a measure for the date difference, the dates are stored in Meeting[Date/Time]).

Thank you 

[Anonymous]

HI @Elisa112,

Did you means the expression are used to calculate with static type values? If that is the case, you can try to use the following measure formula if it suitable for your requirement.

formula =
VAR intro =
    CALCULATE (
        MIN ( Table1[Date] ),
        FILTER ( ALLSELECTED ( Table1 ), [Type] = "Introduction" ),
        VALUES ( Table1[UserID] )
    )
VAR asse =
    CALCULATE (
        MAX ( Table1[Date] ),
        FILTER ( ALLSELECTED ( Table1 ), [Type] = "Assess" ),
        VALUES ( Table1[UserID] )
    )
RETURN
    DATEDIFF ( intro, asse, DAY )

If you mean these results will apply to calculate with dynamic rolling between different type of values, it should difficulty than calculate on specific values.

I'd like to suggest you to add an index filed of the type filed values, then you can use the current index to find out current type and previous type to get difference.

Regards,
Xiaoxin Sheng

[Elisa112]

Thank you, the two dates are static, we use the earliest attended date for each type of meeting and then we need to compare the two to make sure we hit the KPI (less than 5 days).  I will try use solution, the problem I have is now bringing the measure into the maxtrix visual. brinking it into the column does not work.  any Suggestions? 

[Anonymous · Accepted solution]

HI @Elisa112,

Current power bi does not support use measure formula on the axis field or work as category. (you can't use it on row or column field of matrix) They require the category to expand the row contents and calculation ranges or they will only return single value.

Did you means the 'Introduction', 'Assess' are measure expmreison that used on the matrix value field? If that is the case, you can try to use the summarize function of simulation current calculation group and row contents of the expression, then you can add custom field to calculate the diff:

formula =
VAR summary =
    ADDCOLUMNS (
        SUMMARIZE (
            ALLSELECTED ( Table1 ),
            [UserID],
            "_Introduction", [Introduction],
            "_Assess", [Assess]
        ),
        "Diff", DATEDIFF ( [_Introduction], [_Assess], DAY )
    )
RETURN
    SUMX ( summary, [Diff] )

Regards,

Xiaoxin Sheng

[saurabhtd]

@Elisa112 Here is the DAX logic for a measure which might work in your case.  

DateDifference = 
VAR IntroductionDate = CALCULATE(MIN(Meeting[Date/Time]), Meeting[Type] = "Introduction")
VAR AssessDate = CALCULATE(MIN(Meeting[Date/Time]), Meeting[Type] = "Assess")
RETURN
    DATEDIFF(IntroductionDate, AssessDate, DAY)

[Elisa112]

Thank you, I am trying your solution and think it will work, problem is bringing the measure date difference into the matrix, adding the measure as a row/colum or value does not work.  any suggestions?