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.

SamWiseOwl's avatar
SamWiseOwl
Super User
1 year ago

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

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.