Forum Discussion
Line chart rolling average status count
Hi,
I have departmental project data. This includes the below fields and values:
ProjectID: 111, 222, 333
RequestedDate: 10/03/2018, 12/05/2018, 12/05/2018
ResolvedDate: 15/03/2018, 14/05/2018, 05/09/2018
Status: Closed, Closed, Closed
From a user defined RequestedDate, I need to dynamically populate the correct status at that time, for example, if the user selected 13/05/2018, the status' would be 111: Closed, 222: Open, 333: Open. From this date, I need to go back 1 month and calculate the average based on the ProjectID counts for both "Open" and "Closed" status'. This would repeat back through my data on all previous request dates. I then need to plot these average values on a line chart for both Open and Closed status' for a historical comparison.
Any help appreciated.
2 Replies
Well, you are going to need something like these two Quick Measures which figure out a status at a point in time based upon a date or multiple dates within the data.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365Thanks for your reply.
The "Open Tickets" url best suits my requirement but I'm not sure how to add a "Status" column to "tmpTable" beside "ID" and "Date" with the logic: IF [Date] = [Effective Date] THEN "Closed" ELSE "Open"
I would then add "Status" to my line charts legend to produce two comparable line charts based on the [ID] count value.
Any help appreciated.
