Counting Reoccurrences, New and Completed Entries based on month

[misiek5510]

Hi All,

I'm using data as per example table below:

date	user	region	contract
01/01/2021	bob@gmail.com	north	kfc
01/01/2021	steve@gmail.com	north	kfc
01/02/2021	bob@gmail.com	north	kfc
01/02/2021	john@gmail.com	south	kfc
01/02/2021	arnold@gmail.com	south	nasa
01/03/2021	bob@gmail.com	north	kfc

This is an example extract from a reporting tool which shows users that have an outstanding action against them. Using the example above bob@gmail.com from north had an action against him in January as well as Stave, however in February Steve has completed the action as his name is gone, however bob's name is still there along with two new ones (john and Arnold), this would tell us that in February we had 1 completion (Steve), 1 reoccurring entry (bob) and two new entries (John & Arnold). March would show two completions and 1 reoccurring entry (bob). 

As this will be viewed by everyone within our organisation I cannot display the emails address, I would have to use nick name e.g. user1,user2 etc or just using regions and months as summary. e.g.

February:

Comleted:1

Not Completed:1

New:2 

This is the code I'm trying at the moment to see what are the reoccurring entries: 

reoccurring times = countx(SUMMARIZE(DATACOMP,DATACOMP[Month Year],DATACOMP[User ]),DATACOMP[User ])

However it just adds the months together instead of counting just the reoccurring ones.  No clue what it would be for new entries either 

[v-angzheng-msft]

Hi, @misiek5510 

Glad to be helpful to you.

Yes, it will show him as new.

The new measure will be similar to the following, I think

NOT COMPLETED DUE TO STATUS =
IF (
    [Not completed] > 0,
    IF (
        MAX ( user ) IN VALUES ( 'Status'[name] ),
        "NOT COMPLETED DUE TO STATUS",
        "NOT Completed"
    )
)

Best Regards,
Community Support Team _ Zeon Zheng

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

[v-angzheng-msft · Accepted solution]

Hi, @misiek5510 

Result:

Try to create measures as below:

New:

New = 
VAR _PRE_T=FILTER(ALL('Table'),EOMONTH('Table'[date],0)=EOMONTH(MAX('Table'[date]),-1))
VAR _PRE_list=SUMMARIZE(_PRE_T,[user])

VAR _Current_T=FILTER(ALL('Table'),EOMONTH('Table'[date],0)=EOMONTH(MAX('Table'[date]),0))
VAR _Current_list=SUMMARIZE(_Current_T,[user])

VAR _COUNT_PRE_user=if(COUNTROWS(_PRE_list)=BLANK(),0,COUNTROWS(_PRE_list))
VAR _COUNT_CRU_user=if(COUNTROWS(_Current_list)=BLANK(),0,COUNTROWS(_Current_list))

VAR _This_user=MAX('Table'[user])

VAR _New=IF(
    NOT(_This_user IN _PRE_list)&&_This_user IN _Current_list,1,0)
VAR _Rep=IF(
    _This_user IN _PRE_list&&_This_user IN _Current_list,1,0)
VAR _Completed=Countrows(EXCEPT(_PRE_list,_Current_list))

return _New

Not Completed:

NOT Completed = 
VAR _PRE_T=FILTER(ALL('Table'),EOMONTH('Table'[date],0)=EOMONTH(MAX('Table'[date]),-1))
VAR _PRE_list=SUMMARIZE(_PRE_T,[user])

VAR _Current_T=FILTER(ALL('Table'),EOMONTH('Table'[date],0)=EOMONTH(MAX('Table'[date]),0))
VAR _Current_list=SUMMARIZE(_Current_T,[user])

VAR _COUNT_PRE_user=if(COUNTROWS(_PRE_list)=BLANK(),0,COUNTROWS(_PRE_list))
VAR _COUNT_CRU_user=if(COUNTROWS(_Current_list)=BLANK(),0,COUNTROWS(_Current_list))

VAR _This_user=MAX('Table'[user])

VAR _New=IF(
    NOT(_This_user IN _PRE_list)&&_This_user IN _Current_list,1,0)

VAR _Rep=IF(
    _This_user IN _PRE_list&&_This_user IN _Current_list,1,0)
VAR _Completed=Countrows(EXCEPT(_PRE_list,_Current_list))

return _Rep

Count (3):

_count_Completed = 
VAR _PRE_T=FILTER(ALL('Table'),EOMONTH('Table'[date],0)=EOMONTH(MAX('Table'[date]),-1))
VAR _PRE_list=SUMMARIZE(_PRE_T,[user])

VAR _Current_T=FILTER(ALL('Table'),EOMONTH('Table'[date],0)=EOMONTH(MAX('Table'[date]),0))
VAR _Current_list=SUMMARIZE(_Current_T,[user])

VAR _COUNT_PRE_user=if(COUNTROWS(_PRE_list)=BLANK(),0,COUNTROWS(_PRE_list))
VAR _COUNT_CRU_user=if(COUNTROWS(_Current_list)=BLANK(),0,COUNTROWS(_Current_list))

VAR _This_user=MAX('Table'[user])

VAR _New=IF(
    NOT(_This_user IN _PRE_list)&&_This_user IN _Current_list,1,0)

VAR _Rep=IF(
    _This_user IN _PRE_list&&_This_user IN _Current_list,1,0)

VAR _Completed=Countrows(EXCEPT(_PRE_list,_Current_list))

return _Completed

_count_New = 
VAR _ADD_T=ADDCOLUMNS('Table',"_NEW",[New],"_NotCompleted",[NOT Completed])

return SUMX(_ADD_T,[_NEW])

_count_NotCompleted = 
VAR _ADD_T=ADDCOLUMNS('Table',"_NEW",[New],"_NotCompleted",[NOT Completed])

return SUMX(_ADD_T,[_NotCompleted])

Please refer to the attachment below for details. Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

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

[misiek5510]

This worked amazingly thank you very much!

Just got a last quick question: If user appears again in the table in a year or two will it show him as new?
Also, I have a list of blacklisted users in a second table "Status", so I'm looking to add NOT COMPLETED DUE TO STATUS measure so if a user exist in table status then its "NOT COMPLETED DUE TO STATUS" and if not its "NOT Completed". 

[misiek5510]

Also is there a way to too add an dynamic unique ID for the same enties in a column?
For example 

name	id
john	1
steve	2
bob	3
john	1