Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Mal_Sondh
Helper II
Helper II

Project RAG Status overtime

Hi - I would like to show the projects which have moved from Green to Amber, Green to Red, Amber to Red, Amber to Green, Red to Amber and Red to Green over a month on month comparison...

 

Example data - 

MonthProject CodeProject Status
Jan-211234Green
Jan-211212Green
Jan-213333Green
Jan-214444Green
Feb-211234Green
Feb-211212Green
Feb-213333Amber
Feb-214444Red
Mar-211234Amber
Mar-211212Green
Mar-213333Amber
Mar-214444Green

 

If i view the report as of Feb-21 -then i should see a visual with this information:

 Feb-21
Green to Amber3333
Green to Red1212, 4444
Amber to Red 
Amber to Green 
Red to Amber 
Red to Green 

 

This how it would look like when run as of March.

 Mar-21
Green to Amber1234
Green to Red 
Amber to Red 
Amber to Green3333
Red to Amber 
Red to Green4444

 

Whats the best visual for this and best way to model the data to get a good visual for this?

1 ACCEPTED SOLUTION
v-yetao1-msft
Community Support
Community Support

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

Ailsa-msft_0-1621416651392.png

(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] .

Ailsa-msft_1-1621416651393.png

Ailsa-msft_2-1621416651394.png

(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 .

Ailsa-msft_3-1621416651396.png

Ailsa-msft_4-1621416651397.png

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.

View solution in original post

4 REPLIES 4
v-yetao1-msft
Community Support
Community Support

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

Ailsa-msft_0-1621416651392.png

(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] .

Ailsa-msft_1-1621416651393.png

Ailsa-msft_2-1621416651394.png

(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 .

Ailsa-msft_3-1621416651396.png

Ailsa-msft_4-1621416651397.png

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.

@v-yetao1-msft brillaint - thank you so much - this worked as expected!!

Mal_Sondh
Helper II
Helper II

maybe a visual like this:

 

Mal_Sondh_0-1621255524688.png

Note i have not catered for all combination on this example visual.

 

also is there a way of putting the previous months status in another column on the same table.. i.e. something like lookupvalue?

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.