Count entries for a period of time

[Anonymous]

I need to count a "Test Script ID" for the period (each year/month) that the script remained in State=Closed and then once the script changes to State=Decomissioned OR Validated=Out of Scope, I would like it to stop counting the script for the year/month.

See the table below.
Note: The FirstStatusChangedDate is what I'm using in my date slicer.

Example of current table:

Test Script ID	State	Validated	FirstStatusChangedDate
6	Closed	Not Analysed	22/02/2017 15:14
6	Closed	Not Started	22/02/2017 15:14
6	Closed	Ticket	22/02/2017 15:14
6	Closed	Validated	22/02/2017 15:14
6	Decomissioned	Not Analysed	6/04/2020 12:55
8	Closed		22/02/2017 15:14
8	Closed	Being Tested	22/02/2017 15:14
8	Closed	Not Analysed	22/02/2017 15:14
8	Closed	Validated	22/02/2017 15:14
8	Decomissioned	Not Analysed	6/04/2020 12:55
9	Closed		22/02/2017 15:14
9	Closed	Not Started	22/02/2017 15:14
9	Closed	Out of Scope	10/08/2019 15:14
9	Closed	Validated	2/11/2019 15:14
9	Decomissioned	Out of Scope	27/04/2020 14:36
32	Decomissioned	Not Analysed	6/02/2018 15:27
36	Closed	Not Analysed	22/02/2017 15:14
36	Closed	Not Started	22/02/2017 15:14
36	Closed	Validated	22/02/2017 15:14
36	Decomissioned	Not Analysed	6/04/2020 12:51

Expected Result:
Script ID = 6, should show from 22/02/2017 until 05/04/2020 because it got decommisssioned on 06/04/2020 (it should count each month, so if I select Feb/17 it should show the script, if I select Mar/17, if I select Apr/17 it should show the script and so on. But if I just select year=2017 in my slicer, it should count as only 1 script, if I select year=2018, it should count as 1 script and so on for year selection, still showing each month until 05/04/2020, but again if I select year=2020, it should show as 1 script).
Script ID = 8, should show from 22/02/2017 until 05/04/2020.
Script ID = 9, should show from 22/02/2017 until 09/08/2019, then it should stop showing from 10/08/2019 until 01/11/2019 (Validated=Out of Scope) and then show again from 02/11/2019 until 26/04/2020 and then stop showing from 27/04/2020.
Script ID = 32, should never show.
Script ID = 36, should show from 22/02/2017 until 05/04/2020.


Please let me know the step-by-step of where to add your suggestions in Power BI as I'm still very basic.

Thanks

[AllisonKennedy · Accepted solution]

Try adding this as Calculated COLUMNS in DAX:

ScriptRank = RANKX(FILTER(Script, Script[Test Script ID]=EARLIER(Script[Test Script ID])), Script[FirstStatusChangedDate], ,ASC,Dense)

ValidationCount = COUNTROWS(FILTER(Script, Script[Test Script ID]=EARLIER(Script[Test Script ID]) && Script[Validated]="Validated" && Script[ScriptRank]<=EARLIER(Script[ScriptRank] )))

OutOfScopeCount = COUNTROWS(FILTER(Script, Script[Test Script ID]=EARLIER(Script[Test Script ID]) && Script[Validated]="Out of Scope" && Script[ScriptRank]<=EARLIER(Script[ScriptRank] )))

Activity = IF(Script[State]="Decomissioned" || Script[Validated]="Out of Scope" || Script[ValidationCount]&lt;script[OutOfScopeCount] , "Inactive", "Active")

Then filter the visual for when Activity = Active in addition to the date filter you already have.