tracking changes by date column

[Anonymous]

Hi all, Appologies for asking this question again but im struggling to visualise some of the prior examples. 

I have a weekly snapshot of data pulled from our system as per below.

WC	ID	Allocation Date	Complete Date	Cancelled Date	Appointment Date
20/12/2021
27/12/2021	1
03/01/2022	1	04/01/2022
10/01/2022	1	04/01/2022			08/02/2022
17/01/2022	1	04/01/2022			08/02/2022
24/01/2022	1	04/01/2022		30/01/2022	08/02/2022
31/01/2022	1	04/01/2022		30/01/2022	08/02/2022
31/01/2022	2

i am trying to find a way to count changes over each week. ie:

WC	New	Allocated Count	Completed Count	Cancelled Count	Appointment booked Count
20/12/2021	0	0	0	0	0
27/12/2021	1	0	0	0	0
03/01/2022	0	1	0	0	0
10/01/2022	0	0	0	0	1
17/01/2022	0	0	0	0	0
24/01/2022	0	0	0	1	0
31/01/2022	1	0	0	0	0

I assume these will need to be done over a number of calculated columns, but if anyone can help with even 1 example for one columm. i can probably replicate it through the rest.

any help would be extreemly welcomed.

Thanks loads

[Anonymous]

thanks very much 🙂

[v-angzheng-msft · Accepted solution]

Hi, @Anonymous 

Try this:

Allocated Count = 
var _t=SUMMARIZE(ALL('Table'),[Allocation Date],[WC])
var _addRank=ADDCOLUMNS(_t,"_rank",RANKX(FILTER(_t,[Allocation Date]=EARLIER([Allocation Date])),[WC],,ASC))

var _thisDate=MINX('Table',[Allocation Date])
var _that_WC=MINX(FILTER(_addRank,[Allocation Date]=_thisDate&&[_rank]=1),[WC])
var _if=IF(_thisDate=BLANK(),0,
IF(MAX('Table'[WC])=_that_WC,1,0))

return _if

Result:

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.

[lbendlin]

You can do this by unpivoting your data (which will also save you some memory). Please explain the reasoning behind the "New" column - what is the expected number range?  Please provide some more sample data that shows what happens after 31/01/2022.