Forum Discussion

griffinst's avatar
griffinst
Helper I
1 year ago
Solved

Lookup previous month value

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...
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    1 year ago

    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's avatar
    Anonymous
    1 year ago

    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.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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's avatar
    griffinst
    Helper I
    1 year ago

    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" ) )