Forum Discussion

MWare's avatar
MWare
Icon for Helper I rankHelper I
3 years ago
Solved

DAX Calculation to retrieve the entry before pt2

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's avatar
    v-jianboli-msft
    3 years ago

     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's avatar
    MWare
    3 years ago

    Thank you very much this works a treat!

2 Replies

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Icon for Community Support rankCommunity Support
    3 years ago

     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's avatar
      MWare
      Icon for Helper I rankHelper I
      3 years ago

      Thank you very much this works a treat!