Forum Discussion

bgashok's avatar
bgashok
Helper I
2 years ago
Solved

Deviation & comitted calcuation

I am trying to calculate Deviation  & comitted calculation 

 

Comitted graph: x-axis : number of Tasks closed on last day (11/7/23) of iteration from committed (closed state ) / (number of tasks planned to close on first day (10/25/23)) Vs y-axis : Iteration number e.g: 5/14*100 = 35.71% (only first 5 items are closed and rest of them are newly added)/(only 14 are comitted on 25th OCT as 15 & 16 are already closed, exclude close state for count)

 

Deviation graph: number of tasks added after start day / number of committed task Vs Interation number e.g: 9/14 = 64.28% (9 = 21 to 29 are added to iteration after start date 25 Oct)

Table 1: 

IterationIDStateClosed Datecalendar Date
IT11Readynull10/25/2023
IT12Readynull10/25/2023
IT13Readynull10/25/2023
IT14Readynull10/25/2023
IT15Readynull10/25/2023
IT16Developnull10/25/2023
IT17Readynull10/25/2023
IT18Readynull10/25/2023
IT19Newnull10/25/2023
IT110Newnull10/25/2023
IT111Readynull10/25/2023
IT112Developnull10/25/2023
IT113Newnull10/25/2023
IT114Readynull10/25/2023
IT115Developnull10/25/2023
IT116Developnull10/25/2023
IT11Developnull10/27/2023
IT12Newnull10/27/2023
IT13Developnull10/27/2023
IT14Newnull10/27/2023
IT15Readynull10/27/2023
IT16Developnull10/27/2023
IT17Readynull10/27/2023
IT19Readynull10/27/2023
IT110Developnull10/27/2023
IT111Readynull10/27/2023
IT121Developnull10/27/2023
IT122Donenull10/27/2023
IT123Readynull10/27/2023
IT124Readynull10/27/2023
IT125Developnull10/27/2023
IT126Developnull10/27/2023
IT11Closed11/2/202311/3/2023
IT12Developnull11/3/2023
IT13Donenull11/3/2023
IT14Developnull11/3/2023
IT15Developnull11/3/2023
IT16Developnull11/3/2023
IT17Readynull11/3/2023
IT18Developnull11/3/2023
IT19Donenull11/3/2023
IT121Developnull11/3/2023
IT122Developnull11/3/2023
IT123Developnull11/3/2023
IT124Developnull11/3/2023
IT125Developnull11/3/2023
IT126Closed10/30/202311/3/2023
IT127Developnull11/3/2023
IT128Closed11/2/202311/3/2023
IT129Developnull11/3/2023
IT11Closed11/5/202311/7/2023
IT12Closed11/7/202311/7/2023
IT13Closed11/3/202311/7/2023
IT14Closed11/7/202311/7/2023
IT15Closed11/1/202311/7/2023
IT121Closed11/7/202311/7/2023
IT122Developnull11/7/2023
IT123Closed11/7/202311/7/2023
IT124Closed11/7/202311/7/2023
IT125Closed11/7/202311/7/2023
IT126Closed10/30/202311/7/2023
IT127Closed11/7/202311/7/2023
IT128Closed11/7/202311/7/2023
IT129Closed11/7/202311/7/2023

Table 2: 

IterationStart DateEnd Date
IT110/25/202311/7/2023
IT211/8/202311/21/2023
IT311/22/202312/5/2023
IT412/6/202312/19/2023
IT512/20/20231/2/2023
IT61/3/20231/16/2023
Need Help
  • amitchandak's avatar
    amitchandak
    2 years ago

    bgashok , Please find the measures you need

     

    Task Planned = Sumx(Table2, CountX(filter(Table1, Table1[Iteration] = Table2[Iteration] && Table2[Start Date]= Table1[ calendar Date] && [State] <> "closed"), Table1[ID]))

Task Closed = Sumx(Table2, CountX(filter(Table1, Table1[Iteration] = Table2[Iteration] && not(ISBLANK(Table1[Closed Date  ])) && Table1[Closed Date  ] = Table2[End Date] && Table1[ID] in SUMMARIZE(filter(Table1, Table1[Iteration] = Table2[Iteration] && Table2[Start Date]= Table1[ calendar Date] && [State] <> "closed"), Table1[ID])), Table1[ID]))

     

     

     

    File attached after signature

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    bgashok , Please find the measures you need

     

    Task Planned = Sumx(Table2, CountX(filter(Table1, Table1[Iteration] = Table2[Iteration] && Table2[Start Date]= Table1[ calendar Date] && [State] <> "closed"), Table1[ID]))

Task Closed = Sumx(Table2, CountX(filter(Table1, Table1[Iteration] = Table2[Iteration] && not(ISBLANK(Table1[Closed Date  ])) && Table1[Closed Date  ] = Table2[End Date] && Table1[ID] in SUMMARIZE(filter(Table1, Table1[Iteration] = Table2[Iteration] && Table2[Start Date]= Table1[ calendar Date] && [State] <> "closed"), Table1[ID])), Table1[ID]))

     

     

     

    File attached after signature