• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

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
 

Attachments

Chihiro

Excel Ninja
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...
Code:
=
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.
Code:
=
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]
        )
    )
 
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
 
Top