Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Capturing changes over time over 2 weeks

What I am attempting to do is this.. as a report viewer, I want to see the items added after the first day of sprint so that I can see work added after work has begun
Table

Measure to capture the items in the Committed State on the first day of the Sprint

Committed Day 1 = var _date =SELECTEDVALUE('SprintDays'[Date])
var _first_mon  =MAXX( FILTER( ALLSELECTED('SprintDays') , 'SprintDays'[Date] <= _date && 'SprintDays'[Flag] <> BLANK()) ,[Date])
var _first_mon_value = CALCULATE( SUM('Burndown'[StateCat Sort Order]) , 'SprintDays'[Date] =_first_mon)
var _minus=IF( DATEDIFF(_first_mon,_date  ,DAY)>=7 , DATEDIFF(_first_mon,_date  ,DAY)-2,DATEDIFF(_first_mon,_date  ,DAY))
var _weekday=WEEKDAY(_date,2)
return
IF(_weekday in {6,7},BLANK(),_first_mon_value + SUM(Burndown[StateCat Sort Order]))
 
Column to capture the states on the first day of the Sprint
StateCat Sort Order =
SWITCH (  
    'Burndown'[State],  
    "Committed", 1 ,
    "Approved", 2 ,
    3
)
 
What I was hoping to do is filter those items in the committed state (value of 1) on the first day, and then use that column in the measure to calc added work



Need Help

2 Replies

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Community Support
    3 years ago

    Hi Anonymous ,

     

    Based on your description, there does not seem to be a logical error in the formula you provided. Therefore, can you provide the test data corresponding to the data model to facilitate me to answer for you as soon as possible.


    Looking forward to your reply.


    Best Regards,
    Henry

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi v-henryk-mstf ,

    What I want is capture the FIRST DAY that a workitem is in the state committed, but not any of the days after that it stays in the committed state. hopefully the table below will help. As you can see 52676 isn't added until later in the time frame.