Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 -
Month | Project Code | Project Status |
Jan-21 | 1234 | Green |
Jan-21 | 1212 | Green |
Jan-21 | 3333 | Green |
Jan-21 | 4444 | Green |
Feb-21 | 1234 | Green |
Feb-21 | 1212 | Green |
Feb-21 | 3333 | Amber |
Feb-21 | 4444 | Red |
Mar-21 | 1234 | Amber |
Mar-21 | 1212 | Green |
Mar-21 | 3333 | Amber |
Mar-21 | 4444 | Green |
If i view the report as of Feb-21 -then i should see a visual with this information:
Feb-21 | |
Green to Amber | 3333 |
Green to Red | 1212, 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 Amber | 1234 |
Green to Red | |
Amber to Red | |
Amber to Green | 3333 |
Red to Amber | |
Red to Green | 4444 |
Whats the best visual for this and best way to model the data to get a good visual for this?
Solved! Go to Solution.
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.
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.
maybe a visual like this:
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?
User | Count |
---|---|
92 | |
85 | |
77 | |
66 | |
62 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
59 |