Hello
I wonder if someone can help.
We have a KPI that has two elements 1) the first is that a percentage of defects due for resolution within the reporting period must be closed by their due and 2) all defects that remained open from previous reporting periods must be closed i.e no defects open that had a due date before the current reporting period. We report quarterly. I need to graph the KPI values - one graph for the % element and another graph for the 'remain open' element
I am OK with the first element as this value doesnt change at any point after it has been calcuatled so is 'undisturbed' by a refresh. I finding the second element tricky as its moving data and would change with each refresh.
I have read a number of posts already on the forum regardings taking 'snapshots of data' and the suggestion that I have found that is closest to my requirements involves creating a calculated column in the Dates Table using a COUNT type iterative function but I couldnt figure out how to amend the formula to what I need.
The fact table, with information on the defects is called the 'NCR Tracker' and this has a column in it called 'Due Date'. It also has a 'date closed' column, which is blank if the defect has not yet been closed. It has a 'status' column which is either 'Open' or 'Closed' and it has an 'Overdue Status' column which is a calculated column within the SharePoint list which looks at the 'Due Date' and the 'Status' column and today's date and either returns 'Open and Overdue', 'Open but Not Overdue', 'Closed by Due Date' or 'Closed when Overdue'
The dates table is called 'Calendar' Table and is marked at date table using the 'Date' column. There is a one to many relationship between the Calendar Table and the NCR Tracker, which is currently active. Ideally I would like to make this inactive as need to also create a relationship between the 'Date Raised' column in the 'NCR Tracker' and the 'Date' column of the 'Calendar' Table
Anyone know how I could go about 'catching' the snapshot for each quarter, so that it can be graph. I'm pretty new to PowerBi and need a solution that doesnt involve R script or PowerShell (unless you can give me a complete dummies guide to!!)
Many thanks in advance. Much much appreciated.
