Line chart rolling average status count

[Anonymous]

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.

[Greg_Deckler]

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/409365

[Anonymous]

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