Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

tracking changes by date column

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.

 

WCIDAllocation DateComplete DateCancelled DateAppointment Date
20/12/2021     
27/12/2021 1    
03/01/2022 104/01/2022   
10/01/2022 104/01/2022  08/02/2022
17/01/2022 104/01/2022  08/02/2022
24/01/2022 104/01/2022 30/01/202208/02/2022
31/01/2022 104/01/2022 30/01/202208/02/2022
31/01/2022 2    

 

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

 

WCNewAllocated CountCompleted CountCancelled CountAppointment booked Count
20/12/2021 0 0000
27/12/2021 10000
03/01/2022 01000
10/01/2022 00001
17/01/2022 00000
24/01/2022 00010
31/01/2022 10000

 

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

Need Help
Tips and Tricks
  • v-angzheng-msft's avatar
    v-angzheng-msft
    4 years ago

    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.

3 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    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.

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Community Support
    4 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    thanks very much 🙂