Project RAG Status overtime

Hi Mal_Sondh 

There are several problems in your description .

(1)When you view the report as of Feb-21 ,the status for 1212 is still green ,so why put it in “Green to Red” ?

(2)When you view the report as of Mar-21 ,the status for 3333 is still Amber ,so why put it in “Amber to Green” ?

According to the data you provided, I created a sample .

(1)Create a column to return the previous status for the code

previous status = CALCULATE(SELECTEDVALUE('Table'[Project Status]),FILTER(ALLEXCEPT('Table','Table'[Project Code]),EOMONTH('Table'[Month],0)=EOMONTH(EARLIER('Table'[Month]),-1)))

(2)Create a measure to judge whether the 'Table'[Project Status] and 'Table'[previous status] is changed .

Measure = IF(SELECTEDVALUE('Table'[Project Status])<>SELECTEDVALUE('Table'[previous status]) && SELECTEDVALUE('Table'[previous status])<>BLANK(),1,0)

And then put the measure in Filter Pane to return the changed 'Table'[Project Code] .

(3)Create a measure to combine the two status

Status changed = COMBINEVALUES(" to ",SELECTEDVALUE('Table'[previous status]),SELECTEDVALUE('Table'[Project Status]))

And add it in the visual of the second step .You can also add a slicer with 'Table'[Month] to filter the data you want .

I have attached my pbix file, you can refer to it .

Best Regards

Community Support Team _ Ailsa Tao

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