Lookupvalue Last Quarter in the same table without aggregation

[shower999]

Dear all,

I'm new to PowerBI DAX language.

I have a combined each month customer statuses and would like to compare their changes month over month. Customer is unique in each month

essentially, I want to  

1) lookup last month, profile status for the same customer and know the customer profile and warranty date

2) if customer is missing, it's off boarded and I need to flag it out

I am able to do this kind of lookup using Calculate (sum (last_data), DATEADD( date , -1, month)

but it need aggregation. I want this to be done in account level so I can count how many went up and down, warranty extended and slice/ fitler them when necessary.

hope you all can guide me. thank you in advance

Month1	Data_date	Account	Profile	Last_Mth_Profile	Changes	Warranty_date	Last_Mth_W_D	Changes
Jan	1-Jan	Mr. A	1			1-Jan
Feb	1-Feb	Mr. A	3	1	Up	31-Dec	1-Jan	Extended
Mar	1-Mar	Mr. A	3	3		31-Dec	31-Dec
Jan	1-Jan	Mr. B	3			31-Dec
Feb	1-Feb	Mr. B	1	3	Down	1-Jan	1-Jan	Cancelled
Mar	1-Mar				Off boarded			Off boarded

[v-yulgu-msft · Accepted solution]

Hi @shower999,

Please refer to below DAX formulas to add calculated columns:

Index =
RANKX (
    FILTER ( Sample1, Sample1[Account] = EARLIER ( Sample1[Account] ) ),
    Sample1[Data_date],
    ,
    ASC,
    DENSE
)

Last_Mth_Profile =
CALCULATE (
    SELECTEDVALUE ( Sample1[Profile] ),
    FILTER (
        ALLEXCEPT ( Sample1, Sample1[Account] ),
        Sample1[Index]
            = EARLIER ( Sample1[Index] ) - 1
    )
)

Profile_Changes =
IF (
    Sample1[Account] = BLANK (),
    "Off boarded",
    IF (
        Sample1[Last_Mth_Profile] = BLANK ()
            || Sample1[Last_Mth_Profile] = Sample1[Profile],
        BLANK (),
        IF ( Sample1[Last_Mth_Profile] < Sample1[Profile], "Up", "Down" )
    )
)

Last_Mth_W_D =
CALCULATE (
    SELECTEDVALUE ( Sample1[Warranty_date] ),
    FILTER (
        ALLEXCEPT ( Sample1, Sample1[Account] ),
        Sample1[Index]
            = EARLIER ( Sample1[Index] ) - 1
    )
)

Warranty_Changes =
IF (
    Sample1[Account] = BLANK (),
    "Off boarded",
    IF (
        Sample1[Last_Mth_W_D] = BLANK ()
            || Sample1[Last_Mth_W_D] = Sample1[Warranty_date],
        BLANK (),
        IF ( Sample1[Last_Mth_W_D] < Sample1[Warranty_date], "Cancelled", "Extended" )
    )
)

Best regards,

Yuliana Gu

[v-yulgu-msft · Accepted solution]

Hi @shower999,

Please refer to below DAX formulas to add calculated columns:

Index =
RANKX (
    FILTER ( Sample1, Sample1[Account] = EARLIER ( Sample1[Account] ) ),
    Sample1[Data_date],
    ,
    ASC,
    DENSE
)

Last_Mth_Profile =
CALCULATE (
    SELECTEDVALUE ( Sample1[Profile] ),
    FILTER (
        ALLEXCEPT ( Sample1, Sample1[Account] ),
        Sample1[Index]
            = EARLIER ( Sample1[Index] ) - 1
    )
)

Profile_Changes =
IF (
    Sample1[Account] = BLANK (),
    "Off boarded",
    IF (
        Sample1[Last_Mth_Profile] = BLANK ()
            || Sample1[Last_Mth_Profile] = Sample1[Profile],
        BLANK (),
        IF ( Sample1[Last_Mth_Profile] < Sample1[Profile], "Up", "Down" )
    )
)

Last_Mth_W_D =
CALCULATE (
    SELECTEDVALUE ( Sample1[Warranty_date] ),
    FILTER (
        ALLEXCEPT ( Sample1, Sample1[Account] ),
        Sample1[Index]
            = EARLIER ( Sample1[Index] ) - 1
    )
)

Warranty_Changes =
IF (
    Sample1[Account] = BLANK (),
    "Off boarded",
    IF (
        Sample1[Last_Mth_W_D] = BLANK ()
            || Sample1[Last_Mth_W_D] = Sample1[Warranty_date],
        BLANK (),
        IF ( Sample1[Last_Mth_W_D] < Sample1[Warranty_date], "Cancelled", "Extended" )
    )
)

Best regards,

Yuliana Gu

[shower999 · Accepted solution]

WoW!! this works perfectly. highly appreciated. I'll use these formulars across all my calculation.