Find the previous occurrence multiple criteria (date and text criteria) in power pivot

[Lars Ole Sørensen]

I am trying in power pivot to create calculated columns that lookup previous occurances based on multiple crieria.
In the Table example screenshot in the attached workbook for the date 07.02.2021 for the team liverpool I want for example to create 3 calculated columns (Previous 1, Previous 2, Previous 3) that show the result for 3 previous home matches for liverpool.
For previous 1 it looks like when i lookup the previous date I get the correct date, but when I try to lookup the result i get "W" that is not correct (ref power pivot window in workbook)

Hope someone can help me with this.

Kindly Regards

Lars

 

[Chihiro]

In my opinion, this should be done in Power Query stage and not as calculated column.

Your main issue is that you are using MAX() on text field. Since, "W" is intrinsically larger (i.e. higher ASCII value) than "L" or "D", formula will always return "W" if it's found.

One way to do it...

=
RIGHT (
    CONCATENATEX (
        FILTER (
            Data,
            EARLIER ( Data[Home team] ) = Data[Home team]
                && EARLIER ( Data[Date] ) > Data[Date]
        ),
        Data[Home Result]
    ),
    1
)

Alternately... this should be more performant.

=
VAR myDate =
    CALCULATE (
        MAX ( Data[Date] ),
        FILTER (
            Data,
            EARLIER ( Data[Home team] ) = Data[Home team]
                && EARLIER ( Data[Date] ) > Data[Date]
        )
    )
RETURN
    CALCULATE (
        LASTNONBLANK ( Data[Home Result], 1 ),
        FILTER (
            Data,
            EARLIER ( Data[Home team] ) = Data[Home team]
                && myDate = Data[Date]
        )
    )

 

[Lars Ole Sørensen]

Ok, I will look into the option to do this in power query, thanks for the advise and explanations why max didnt do the trick. The two code alternatives did to the trick in power pivot. Thank you very much for the help and assistanse

Regards
Lars

 

[p45cal]

In the attached, a pure Power Query offering at cell AE4.
Right-click the table and choose Refresh to update it.
Currently hard-coded for 4 previous results but this can be made more flexible.

 

[Lars Ole Sørensen]

Hi p45cal. Fantastic, exactly what I wanted to achieve, this is flexible enough for me, thank you very much for the help