DAX Calculation to retrieve the entry before pt2

[MWare]

Hi,

Looking for some help please.

I am trying to return the previous 'Sale Product' based on the 'Sales Person' of each row in my 'Sales' table.

Unfortunatley the index is based on all sales in the shop and thus I can't use -1 on the index as the Sales Person can vary.

See below image in Power BI, the DAX formula I have created using EARLIER in column 'Prior Sales Product (DAX Help)' is not returning the prior sales product for the sales person for the row.  See the 'Prior Sales Product (the answer I'm after)' column for what the value I'm expecting in my DAX formula.

Any ideas?

 @wdx223_Daniel2  might be another easy one for you as you managed to solve the previous query

[v-jianboli-msft · Accepted solution]

 Hi @MWare ,

Please try:

Column =
VAR _a =
    MAXX (
        FILTER (
            'Table',
            [Sale Person] = EARLIER ( 'Table'[Sale Person] )
                && [Shop Sale Index] < EARLIER ( 'Table'[Shop Sale Index] )
        ),
        [Shop Sale Index]
    )
VAR _b =
    MAXX (
        FILTER (
            'Table',
            [Sale Person] = EARLIER ( 'Table'[Sale Person] )
                && [Shop Sale Index] = _a
        ),
        [Sale Product]
    )
RETURN
    _b

Final output:

Best Regards,

Jianbo Li

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

[v-jianboli-msft · Accepted solution]

 Hi @MWare ,

Please try:

Column =
VAR _a =
    MAXX (
        FILTER (
            'Table',
            [Sale Person] = EARLIER ( 'Table'[Sale Person] )
                && [Shop Sale Index] < EARLIER ( 'Table'[Shop Sale Index] )
        ),
        [Shop Sale Index]
    )
VAR _b =
    MAXX (
        FILTER (
            'Table',
            [Sale Person] = EARLIER ( 'Table'[Sale Person] )
                && [Shop Sale Index] = _a
        ),
        [Sale Product]
    )
RETURN
    _b

Final output:

Best Regards,

Jianbo Li

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

[MWare · Accepted solution]

Thank you very much this works a treat!