Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi All,
New to Power BI, wanted to check which visual i need to use to show last month status and last-to-last month status of the requests. Business requirement is to know the requests whose status didnt changed for the last two months, so that they can track and ask respective request owner to work on the item. The status are:
1. In Progress
2. Pending with IT Team
3. Pending with Reviewer
4. Pending with Auditor
5. Closed
Hi @Anonymous ,
According to your description, I made a sample and here is my solution.
Sample data:
Create two columns to return status of last month and last two month.
status of last month =
var _a = CALCULATE(MAX('Table'[DATE]),FILTER('Table','Table'[case]=EARLIER('Table'[case])))
return IF('Table'[DATE]>= DATE(YEAR(_a),MONTH(_a)-1,DAY(_a)),'Table'[status])
status of last two months =
var _a = CALCULATE(MAX('Table'[DATE]),FILTER('Table','Table'[case]=EARLIER('Table'[case])))
return IF('Table'[DATE]>= DATE(YEAR(_a),MONTH(_a)-2,DAY(_a)),'Table'[status])
Then create two columns to judge whether the status changed last month(last two months). If not changed, then return 1.
last month =
var _a = CALCULATE(MAX('Table'[DATE]),FILTER('Table','Table'[case]=EARLIER('Table'[case])))
return
IF('Table'[DATE]=_a,IF(CALCULATE(DISTINCTCOUNTNOBLANK('Table'[status of last month]),FILTER('Table','Table'[case]=EARLIER('Table'[case])))=1,1,0))
last two months =
var _a = CALCULATE(MAX('Table'[DATE]),FILTER('Table','Table'[case]=EARLIER('Table'[case])))
return
IF('Table'[DATE]=_a,IF(CALCULATE(DISTINCTCOUNTNOBLANK('Table'[status of last two months]),FILTER('Table','Table'[case]=EARLIER('Table'[case])))=1,1,0))
Then you can use a stacked column chart to see clearly whose status didn't change as below.
I attach my sample below for your reference.
Best Regards,
Community Support Team _ xiaosun
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Thank you for your response. I tried to replicate the same. However i found couple of issues, especially when all the status changes happened on same day, and for one case there is only one status.
Sample Data:
Case G: has only one entry. So It should have value of last month status, but should not have value for last to last month.
Case H: How to represent if all the status changes are within one day?
The current code is giving me the below chart. Please advice.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
76 | |
74 | |
56 | |
45 |
User | Count |
---|---|
117 | |
105 | |
77 | |
66 | |
64 |