Forum Discussion
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 | 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 |
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"))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.
9 Replies
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.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 ))RETURNIF ( _priorStatus <> BLANK (), IF ( 'MOM'[Affordability of Care Initiatives] = _priorStatus, "F", "T" ) )
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)RETURNif(currStatus <> lastStatus && lastStatus <> BLANK(),"T","F")griffinst Edited to included Status change T or F
I need these to be columns not measures.
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)RETURNif(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.
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"))
