Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Counting item data based on former status

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 cl...
Need Help
  • v-zhangti's avatar
    v-zhangti
    3 years ago

    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

v-zhangti's avatar
v-zhangti
Community Support
3 years ago

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