Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Active based on status change

I have the below table. I am trying to calculate the active IDs as of every month end. Tried subtracting distinct ID count-distinct resolved count but that doesn't accommodate cases where IDs are reactivated(like ID 4 in the below dataset). please help.

IDCreateDateStatus change datechangeDateEOMStatus
13-Jan-221/12/202231-Jan-22Active
25-Jan-221/13/202231-Jan-22Active
25-Jan-222/2/202228-Feb-22Resolved
35-Jan-221/5/202231-Jan-22Active
35-Jan-222/20/202228-Feb-22Resolved
45-Jan-221/5/202231-Jan-22Active
45-Jan-222/3/202228-Feb-22Resolved
45-Jan-223/3/202231-Mar-22Active
55-Jan-221/5/202231-Jan-22Active
55-Jan-222/3/202228-Feb-22Resolved
55-Jan-223/3/202231-Mar-22Resolved
65-Jan-222/3/202228-Feb-22Active
65-Jan-223/3/202231-Mar-22Resolved

 

expected result

endofmonthActiveCount
31-Jan-226
28-Feb-222
31-Mar-222

 

 

notes
All 6 IDs are active by end of jan
1&6 are active by end of feb
1&4 are active by end of mar(4 was reactivated)
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    CNENFRNL Thank you so much for this. Is there a way to make this measure work when I am slicing it using other columns. For example the term column like the below? 

    IDCreateDateStatus change datechangeDateEOMStatusTerm
    11/3/20221/12/20221/31/2022ActiveShort Term
    21/5/20221/13/20221/31/2022ActiveShort Term
    21/5/20222/2/20222/28/2022ResolvedMedium Term
    31/5/20221/5/20221/31/2022ActiveShort Term
    31/5/20222/20/20222/28/2022ResolvedShort Term
    41/5/20221/5/20221/31/2022ActiveShort Term
    41/5/20222/3/20222/28/2022ResolvedShort Term
    41/5/20223/3/20223/31/2022ActiveLong Term
    51/5/20221/5/20221/31/2022ActiveMedium Term
    51/5/20222/3/20222/28/2022ResolvedMedium Term
    51/5/20223/3/20223/31/2022ResolvedMedium Term
    61/5/20222/3/20222/28/2022ActiveShort Term
    61/5/20223/3/20223/31/2022ResolvedShort Term

     

    expected result - return the term count for active cases

    End of monthLong TermMedium TermShort Term
    31-Jan-22015
    28-Feb-22002
    31-Mar-22101