Previous date of occurrence

[Anonymous]

Hello

I'm trying to get the date of the last occurrence.

Example

Employee ID	Date of visit	Discipline
1	9/1/2021	RN
1	9/6/2021	CNA
1	9/10/2021	RN
1	9/25/2021	RN
2	9/5/2021	RN
2	9/10/2021	RN

What I Need RN Last Visit Date or Example Column #4

Employee ID	Date of visit	Discipline	Previous occurrence
1	9/1/2021	RN
1	9/6/2021	CNA
1	9/10/2021	RN	9/1/2021
1	9/25/2021	RN	9/10/2021
2	9/5/2021	RN
2	9/10/2021	RN	9/5/2021

the ultimate goal is to find the number of days between each visit, but I thought I needed to find the previous occurrence first. then perform a datediff measurement

Thanks in advance

[Anonymous]

Hi @Anonymous,

Did these suggestions help with your scenario? if that is the case, you can consider Kudo or accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[Anonymous]

Hi, nope. None of the suggestions helped get what I was looking for. 

[Anonymous]

Hi @Anonymous,

So you mean you need a calculated column to do some calculations in direct query mode? AFAIK, most DAX functions have been limited to use in calculated column/table if you are working with 'direct query' mode.

What type of data source are you worked on? If it supports advanced queries, you can try to add a custom column in the query.

Regards,

Xiaoxin Sheng

[ERD]

Hi @Anonymous ,

Did you try to create a measure? For example,

days = 
VAR currentEmpID = MAX ( T[EmployeeID] )
VAR currentDisc = MAX ( T[Discipline] )
VAR currentDate = MAX ( T[VisitDate] )
VAR prevDate =
    CALCULATE (
        MAX ( T[VisitDate] ),
        FILTER (
            ALL ( T ),
            T[EmployeeID] = currentEmpID
                && T[Discipline] = currentDisc
                && T[VisitDate] < currentDate
        )
    )
RETURN
    COALESCE ( DATEDIFF ( prevDate, currentDate, DAY ), 0 )

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

[CNENFRNL]

Previous occurrence = 
MAXX(
    FILTER(
        VISIT,
        VISIT[Employee ID] = EARLIER( VISIT[Employee ID] )
            && VISIT[Discipline] = EARLIER( VISIT[Discipline] )
            && VISIT[Date of visit] < EARLIER( VISIT[Date of visit] )
    ),
    VISIT[Date of visit]
)

[Anonymous]

Can't use MAXX in Direct Query either

[parry2k]

@Anonymous try adding this column 

Prev Visit Date = 
VAR __d = Visit[Visit Date]
RETURN
IF ( Visit[Discipline] = "RN",
    CALCULATE ( 
        MAX ( Visit[Visit Date] ), 
        Visit[Visit Date] < __d, 
        Visit[Employee ID] = EARLIER ( Visit[Employee ID] ) 
    ) 
)

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

This is the error message i am receiving. I am unable to change from DirectQuery.