How do I create "Result from Prior Month"

[Amos_Mc]

I want to monitor the movement of clients who have moved from an "Onboarding" state to a "Retain & Grow" state
I have reviewed previous posts and yet I am unable to create the required result

Here is the DAX I am using right now

State Prior Month = CALCULATE (

    MAX ( 'Customer Life Cycle State'[State Name] ),

    FILTER ( 'Date', 'Date'[Month Number] =  ('Date'[Month Number]) - 1 )

)

Sample table extract below with result of current DAX showing the same state as current

Client ID	Month Year	Month Number	State Name (Current Month)	State Name (Prior Month)	Performing Advertiser Flag		Result needed for PRIOR Month
12345	Jun-22	6	ONBOARDING	ONBOARDING	1		ONBOARDING
12345	Jul-22	7	ONBOARDING	ONBOARDING	1		ONBOARDING
12345	Aug-22	8	ONBOARDING	ONBOARDING	1		ONBOARDING
12345	Sep-22	9	RETAIN & GROW	RETAIN & GROW	1		ONBOARDING
12345	Oct-22	10	RETAIN & GROW	RETAIN & GROW	1		RETAIN & GROW
12345	Nov-22	11	RETAIN & GROW	RETAIN & GROW	1		RETAIN & GROW
12345	Dec-22	12	RETAIN & GROW	RETAIN & GROW	1		RETAIN & GROW
12345	Jan-23	1	RETAIN & GROW	RETAIN & GROW	1		RETAIN & GROW
12345	Feb-23	2	RETAIN & GROW	RETAIN & GROW	1		RETAIN & GROW
12345	Mar-23	3	RETAIN & GROW	RETAIN & GROW	1		RETAIN & GROW

[eliasayyy]

great news! i found a solution by measure

first add the month id column(this will help you for later when theres multiple years and same month number)

use this measure

Measure = 
VAR ThisMonthID =
    MAX ( 'Table'[Month ID] )
VAR result =
    CALCULATE (
        MAX ( 'Table'[Status] ),ALLEXCEPT('Table'[Client ID]),
        'Table'[Month ID] = ThisMonthID - 1
    )
RETURN
IF(ISBLANK(result),"",result)

the all except will filter out all the columns and keep the main focus on client ID so if you want to be making it by Client id and a sub category , add the sub category to the ALLEXCEPT expression

and i added another client id just in case if you have multple and switch the sate name a bit

a kudos would be appreciated

[eliasayyy]

hello i dont know how to do it in measure but it can be done with calculated column

step 1 :
add month id index 

VAR _min = YEAR(MIN('Table'[Date]))
RETURN
MONTH('Table'[Date]) + (YEAR('Table'[Date]) - _min) *12

Step 2 
add a new column with the expressions

Previous Return = CALCULATE(MAX('Table'[State Name]),FILTER('Table','Table'[Month ID] = EARLIER('Table'[Month ID]) -1 ))

Result:

[Amos_Mc]

Thanks for your suggestion
I tried applying this solution using the DAX below and I was unable to get the desired result

It still delivered the current month status not the prior month

State99 =

VAR ThisMonthID =

    MAX ('Date'[Month Number] )

VAR result =

    CALCULATE (

        MAX ('Customer Life Cycle State'[State Name] ),ALLEXCEPT('Advertiser',Advertiser[Advertiser CID]),

        'Date'[Month Number] = ThisMonthID - 1

    )

RETURN

IF(ISBLANK(result),"",result)

[eliasayyy]

Please check my solution i advised to create a new calculated column called momth id and use month id instead of month number

[amosmc]

I would have liked to use your column solution. Unfortunately I am using a live connect and even with a limited composite model I am unable to add a column to my large dataset. 

[ValtteriN]

Hi,

Here is one way to do this:

Measure 47 =

CALCULATE ( MAX('Table (32)'[State Name (Current Month)]), DATEADD ( 'Calendar'[Date], -1, MONTH ) )

End result:
I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[Amos_Mc]

Thanks for your suggestion
I tried applying this solution using the DAX below and I was unable to get the desired result

It still delivered the current month status not the prior month

State 47 ver = CALCULATE ( MAX ('Customer Life Cycle State'[State Name]), DATEADD ( 'Date'[Calendar Date], - 1 , MONTH ) )

[Amos_Mc]

Thanks for your suggestion
I tried applying this solution using the DAX below and I was unable to get the desired result

It still delivered the current month status not the prior month

State 47 ver = CALCULATE ( MAX ('Customer Life Cycle State'[State Name]), DATEADD ( 'Date'[Calendar Date], - 1 , MONTH ) )