Dax to obtain Last Date, Result of Last Date, Second to Last Date, Result of Second to Last Date

[icedavies]

Hi all,

Hoping someone much smarter than me can help!

Basically I have a table which is constantly being updated with more entries.  There are many duplicate references which will have many resuls and visit dates. A snapshot of the data is below:

As part of my analysis I want to compare the last 2 visits only (I want this to be dynamic so in a months time the data will change). I would like a table showing the columns like in the image below. 
Last Visit Date = The date the reference was visited last.
Last Result = The Result of the Last Visit
Second to Last Visit Date = The visit date before the last visit date
Second to Last = The Result of the Second to Last Visit.
No. of Days Difference = The number of days between the last and second to last visit.

I managed to get a version of the Dates columns but I don't think it was ideal. I think the method I used is what's stopping me from getting the result which I also need.  Anyone have any suggestions please?
Thanks

[techies]

Hi @icedavies please check this

create a calculated column to rank the visits--   

Visit Rank =

RANKX(

    FILTER(Visits, Visits[Reference] = EARLIER(Visits[Reference])),

    Visits[Visit Date],

    ,

    DESC

)

Then, create these measures

Last Visit Date =

CALCULATE(

    MAX(Visits[Visit Date]),

    FILTER(Visits, Visits[Visit Rank] = 1)

)

Last Result =

CALCULATE(

    MAX(Visits[Result]),

    FILTER(Visits, Visits[Visit Rank] = 1)

)

Second to Last Visit Date =

CALCULATE(

    MAX(Visits[Visit Date]),

    FILTER(Visits, Visits[Visit Rank] = 2)

)

Second to Last Result =

CALCULATE(

    MAX(Visits[Result]),

    FILTER(Visits, Visits[Visit Rank] = 2)

)

No of Days Difference =

DATEDIFF(

    [Second to Last Visit Date],

    [Last Visit Date],

    DAY

)

[icedavies]

Sorry my bad @techies - it must have been a typo on my part, it worked at the 2nd attempt.

OK so now I have another problem.  When I'm writing the Last Result measure, I am getting the below message: "A single value for column 'Flow' in table 'Invoices Master' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."
There are duplicate records in here which I am reluctant to remove for now as we are being charged for the double visits (which I will raise separately). Am I better off duplicating the whole table and then removing the entries with same day visits and conducting the analysis on that table?
Assuming that this is what is causing the problem?

[techies]

ok, may be not now, as it reflects all billable visits even on the same day, you can try this measure if this works, this will pick the highest result as i understand

Last Result =
MAXX(
FILTER(Visits, Visits[VisitRank] = 1),
Visits[Result]
)

[icedavies]

Thanks @techies , I'm getting so close!  The measures seem to work but now having difficulty with the Second to Last Visit Date. I'm getting this error message "A single value for column 'Read Date' in table 'Invoices Master' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.
I can only assume that there are multiple values for 2nd to last date but not for the last date?

[techies · Accepted solution]

Second to Last Visit Date =
MAXX(
FILTER(Visits, Visits[Visit Rank] = 2),
Visits[Visit Date]
)

Second to Last Result =
MAXX(
FILTER(Visits, Visits[Visit Rank] = 2),
Visits[Result]
)

[icedavies]

You're a star thanks for that!  I would never have come up with that solution by myself.  Thanks again

[techies]

You're welcome, happy to support 🙂

[icedavies]

Thanks @techies  It's not allowing me to add the Visit Rank column.  I'm getting an error message saying "Special flag is not allowed as an argument number 3 of function 'RANKX'."  ??

[techies]

like this ? 

[icedavies]

Thank you @Jihwan_Kim .  The dates part work great.  Do you have any idea on how can I get the result of the visit to go with it? I need the result of the last visit and the result of the 2nd to last visit by the side of each date.
The result of the visit date is crucial to the analysis. Thanks

[Jihwan_Kim]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

I tried to use INDEX dax function in the measures.

INDEX function (DAX) - DAX | Microsoft Learn

last date: = 
VAR _t =
    SUMMARIZE ( data, 'calendar'[Date] )
RETURN
    MAXX ( INDEX ( 1, _t, ORDERBY ( 'calendar'[Date], DESC ) ), 'calendar'[Date] )

second last date: = 
VAR _t =
    SUMMARIZE ( data, 'calendar'[Date] )
RETURN
    MAXX ( INDEX ( 2, _t, ORDERBY ( 'calendar'[Date], DESC ) ), 'calendar'[Date] )

diff: = 
DATEDIFF([second last date:], [last date:], DAY )