Derive open defects from the past using Dax

[Hyuna_8000]

Hi,

I have a requirement to calculate open defects in the past months based on the following sample datasets. Notes that all three tables are pulled from SQL server DB, "All defect" table is the original table, "New" and "Closed" defect count tables are summarised tables based on "All defect" table using power query.

All defects (existing table)

Defect Number	Date Logged	Closed Date	Status
Def001	4/01/2022		Work in progress
Def002	10/01/2022		Ready
Def003	11/01/2022	29/03/2022	Closed
Def004	11/01/2022	1/03/2022	Closed
Def005	12/01/2022		Work in progress
Def006	13/01/2022	31/01/2022	Closed
Def007	1/02/2022	28/04/2022	Closed
Def008	2/02/2022		Ready
Def009	2/02/2022		Ready
Def010	3/02/2022	4/04/2022	Closed
Def011	4/02/2022		Work in progress
Def012	7/02/2022		Assigned
Def013	8/02/2022	28/02/2022	Closed
Def014	1/03/2022	5/04/2022	Closed
Def015	1/03/2022	28/04/2022	Closed
Def016	2/03/2022		Ready
Def017	23/03/2022	24/03/2022	Closed
Def018	24/03/2022		Ready
Def019	25/03/2022	27/04/2022	Closed
Def020	4/04/2022	13/04/2022	Closed
Def021	5/04/2022		Ready
Def022	11/04/2022	26/04/2022	Closed
Def023	12/04/2022	29/04/2022	Closed
Def024	29/04/2022		Ready
Def025	29/04/2022		Ready
Def026	2/05/2022		Work in progress
Def027	3/05/2022		Ready
Def028	4/05/2022		Ready
Def029	5/05/2022	6/05/2022	Closed
Def030	9/05/2022		Ready

Count of new defects every month (existing table, I use end of month to represent the count from the entire month)

Date Logged	New Defect Count
31/01/2022	6
28/02/2022	7
31/03/2022	6
30/04/2022	6
10/05/2022	5

Count of closed defects every month (existing table)

Closed Date	Closed Defect Count
31/01/2022	1
28/02/2022	1
31/03/2022	3
30/04/2022	8
31/05/2022	1

In this case, the definition of "Open defects" means any defects that is still in "Assigned" or "Work in progress" or "Ready" status. Based on this, I can calculate the current open defects as of now, which is 16 according to the sample data.

And from here, I want to go backward and calculate open defect from past months. The logic of deriving open defects from past months should be:

Current open defects (as of today 10th May 2022) =16

Open defects in Apr 2022= 16 + Closed defects in May 2022 - New defects in May 2022

Open defects in Mar 2022= Open defects in Apr 2022 + Closed defects in Apr 2022 - New defects in Apr 2022

Open defects in Feb 2022= Open defects in Mar 2022 + Closed defects in Mar 2022 - New defects in Mar 2022

Open defects in Jan 2022= Open defects in Feb 2022 + Closed defects in Feb 2022 - New defects in Feb 2022

Open defects in Dec 2021= Open defects in Jan 2022 + Closed defects in Jan 2022 - New defects in Jan 2022

I would like to have a dynamic table using DAX, meaning although now is May, I am expecting in the future I can see open defects from May, Apr etcs in June, and open defects from June, May in July etcs..

Output: Open defects by months

Month	Open Defect Counts
Current (as of 10052022)	16
30/04/2022	12
31/03/2022	14
28/02/2022	11
31/01/2022	5
31/12/2021	0
..	..
..	..

Not sure how complicated this can be, I am still learning DAX and struggling, any ideas would be greatly appreciated. Thanks

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but please check the below attached pbix file.

Count Open Defect: = 
IF (
    HASONEVALUE ( 'Calendar'[Month Name] ),
    IF (
        MIN ( 'Calendar'[Date] ) <= TODAY (),
        COUNTROWS (
            FILTER (
                Data,
                Data[Date Logged] <= MAX ( 'Calendar'[Date] )
                    && OR (
                        ISBLANK ( Data[Closed Date] ),
                        Data[Closed Date] > MAX ( 'Calendar'[Date] )
                    )
            )
        )
    )
)

[Hyuna_8000]

Thanks @tamerj1 and @Jihwan_Kim . Both of the DAX formulas return the correct value.

[tamerj1 · Accepted solution]

HI @Hyuna_8000 
Other way of doing that using SUMX https://www.dropbox.com/t/Wdk5XtFV5jyu7cBd

Open Defect Counts = 
VAR LastDateInPeriod = MAX ( 'Date'[Date] )
RETURN
    SUMX (
        Defects,
        VAR StartDate = Defects[Date Logged]
        VAR EndDate = Defects[Closed Date]
        RETURN 
            IF ( 
                StartDate <= LastDateInPeriod 
                    && OR ( ISBLANK ( EndDate ), EndDate > LastDateInPeriod ),
                1
            )
    )

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but please check the below attached pbix file.

Count Open Defect: = 
IF (
    HASONEVALUE ( 'Calendar'[Month Name] ),
    IF (
        MIN ( 'Calendar'[Date] ) <= TODAY (),
        COUNTROWS (
            FILTER (
                Data,
                Data[Date Logged] <= MAX ( 'Calendar'[Date] )
                    && OR (
                        ISBLANK ( Data[Closed Date] ),
                        Data[Closed Date] > MAX ( 'Calendar'[Date] )
                    )
            )
        )
    )
)