Lookup previous month value

[griffinst]

I need to create 2 columns.  One that shows the Prior Month FCST value from the same project and another that shows if the "Status" changed on that project.

FCST Date	Status	Project	FCST	Prior Month	Status Change from Prior Month
1/1/2024	In-Forecast	Alpha	100
1/1/2024	In-Forecast	Gamma	200
1/1/2024	In-Ideation	Sigma	300
2/1/2024	In-Forecast	Alpha	400	100	F
2/1/2024	In-Forecast	Gamma	500	200	F
2/1/2024	In-Forecast	Sigma	600	300	T
3/1/2024	In-Forecast	Alpha	700	400	F
3/1/2024	In-Forecast	Gamma	800	500	F
3/1/2024	In-Forecast	Sigma	900	600	F

[ryan_mayu · Accepted solution]

@griffinst 

pls try this

Column = MAXX(FILTER('Table','Table'[Project]=EARLIER('Table'[Project])&&'Table'[FCST Date]=EDATE(EARLIER('Table'[FCST Date]),-1)),'Table'[FCST])

Column 2 =

VAR _status=MAXX(FILTER('Table','Table'[Project]=EARLIER('Table'[Project])&&'Table'[FCST Date]=EDATE(EARLIER('Table'[FCST Date]),-1)),'Table'[Status])

return if(_status="","",if(_status='Table'[Status],"F","T"))

[Anonymous · Accepted solution]

Hi @griffinst 

Maybe you can try this:

Here I create 2 calculated columns:

_Prior Month =
CALCULATE (
    SUM ( 'Table'[FCST] ),
    ALLSELECTED ( 'Table' ),
    'Table'[Project] = EARLIER ( 'Table'[Project] ),
    DATEADD ( 'Table'[FCST Date], -1, MONTH )
)

_Status Change? =
VAR _priorStatus =
    CALCULATE (
        SELECTEDVALUE ( 'Table'[Status] ),
        ALLSELECTED ( 'Table' ),
        'Table'[Project] = EARLIER ( 'Table'[Project] ),
        DATEADD ( 'Table'[FCST Date], -1, MONTH )
    )
RETURN
    IF ( _priorStatus <> BLANK (), IF ( 'Table'[Status] = _priorStatus, "F", "T" ) )

The result is as follow:

Best Regards

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

[griffinst]

I got the Prior Month working.  Thank you very much.  I had to add the Business Area and LOB to the formula as it needs to match the "Project", "Business Area", and "LOB" of the prior month.

Prior Month =

CALCULATE (

    SUM ( 'MOM'[Forecast] ),

    ALLSELECTED ( 'MOM' ),

    'MOM'[Project] = EARLIER ( 'MOM'[Project] ),

    'MOM'[Business Area] = EARLIER ( 'MOM'[Business Area] ),

    'MOM'[LOB] = EARLIER ( 'MOM'[LOB] ),

    DATEADD ( 'MOM'[FCST Date], -1, MONTH )

)

For the Status Change though I added the same for Business Area and LOB but it is not working as expected.

Status Change =

VAR _priorStatus =

    CALCULATE (

        SELECTEDVALUE ( 'MOM'[Project Status] ),

        ALLSELECTED ( 'MOM' ),

        'MOM'[Project] = EARLIER ( MOM[Project] ),

        'MOM'[Business Area] = EARLIER ( MOM[Business Area] ),

        'MOM'[LOB] = EARLIER ( MOM[LOB] ),

        DATEADD ( 'MOM'[FCST Date], -1, MONTH )

    )

RETURN

    IF ( _priorStatus <> BLANK (), IF ( 'MOM'[Affordability of Care Initiatives] = _priorStatus, "F", "T" ) )

[ryan_mayu · Accepted solution]

@griffinst 

pls try this

Column = MAXX(FILTER('Table','Table'[Project]=EARLIER('Table'[Project])&&'Table'[FCST Date]=EDATE(EARLIER('Table'[FCST Date]),-1)),'Table'[FCST])

Column 2 =

VAR _status=MAXX(FILTER('Table','Table'[Project]=EARLIER('Table'[Project])&&'Table'[FCST Date]=EDATE(EARLIER('Table'[FCST Date]),-1)),'Table'[Status])

return if(_status="","",if(_status='Table'[Status],"F","T"))

[griffinst]

I got it working with a combination from both of you.  thank you very much.

[ryan_mayu]

you are welcome

[SamWiseOwl]

Hi @griffinst 

You can create a measure using DATEADD to move the values:

Last month =

CALCULATE(

    sum('Status'[FCST])

    ,ALLSELECTED('Status'[Status]) --remove status filter

    ,DATEADD('Status'[FCST Date],-1, MONTH) --move back 1 month

)

What do you want to return if it has changed?
At a guess it would be:

Status Last month =

var currStatus = SELECTEDVALUE('Status'[Status])

var lastStatus =

CALCULATE(

   SELECTEDVALUE('Status'[Status])

  ,ALLSELECTED('Status'[Status]) --remove status filter

    ,DATEADD('Status'[FCST Date],-1, MONTH) --move back 1 month

)

RETURN

if(

    currStatus <> lastStatus && lastStatus <> BLANK()

    ,"T"

    ,"F"

)

[griffinst]

This did not work for the Status Change "T" or "F" column.  It's showing "F" for everything even though I change one of the status'.

Status Last month =

var currStatus = SELECTEDVALUE('Status'[Status])

var lastStatus =

CALCULATE(

   SELECTEDVALUE('Status'[Status])

  ,ALLSELECTED('Status'[Status]) --remove status filter

    ,DATEADD('Status'[FCST Date],-1, MONTH) --move back 1 month

)

RETURN

if(

    currStatus <> lastStatus && lastStatus <> BLANK()

    ,"T"

    ,"F"

)

The first Measure you suggested for the "Prior Month FCST" does not work.  I just want the value from the prior month from the FCST column where the "Project" matches of course.

[griffinst]

I need these to be columns not measures.

[SamWiseOwl]

@griffinst  Edited to included Status change T or F