Counting item data based on former status

[Anonymous]

Dear community, 

I have 2 tables, a "Fiscal Year" date table and a table with items that change their status over time (see below) e.g. 

Status open when there is a " date created" and Status closed when there is a "Closed date".

I need to be able to count the number of items that were "open" based on the date I filter:

Example below:

If I filter on October 8th 2022 it should count:

All items that have a "Date Created" => 10.08.22 with no "Closed Date" (the ones with the status Open)

+

all with a "closed Date" > 10.08.22 && "Date Created" <= 10.08.22 (The ones with the status filled but had the status "open" on the date of filter)

Somehow I have not been lucky with several solutions from slightly similar issues in the community. 

Thank you very much in advance for any clue!!

Svendu 

[Anonymous]

Thank you very much for your prompt and helpful reply on this issue. 

Svendu

[v-zhangti · Accepted solution]

Hi, @Anonymous 

You can try the following methods.

Measure = 
VAR _N1 =CALCULATE (COUNT ( 'Table'[ID Number] ),
          FILTER (ALL ( 'Table' ),
            [Date Created] >= SELECTEDVALUE ( 'Date'[Date] )
                && [Closed Date] = BLANK ()
                && [Status] = "Open"
        )
    )
VAR _N2 =CALCULATE (COUNT ( 'Table'[ID Number] ),
        FILTER (ALL ( 'Table' ),
            [Date Created] <= SELECTEDVALUE ( 'Date'[Date] )
                && [Closed Date] >= SELECTEDVALUE ( 'Date'[Date] )
                && [Status] = "Filled"
        )
    )
RETURN
    _N1 + _N2

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly