Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

scope increase and decrease

So i have a table with workitems,datechange,sprint id, sprint start , sprint end. i need to calculate the number of workitems that are added or removed ON or AFTER the second day of the sprint start (sprint =14days) 

|workitems| |sprint id| |datechange|                   sprint start      sprint end

20212  abckd1 22-01-2020 16:35:16  21-01-2020 03-02-2020
23233 abckd1 21-01-2020 16:35:16  21-01-2020 03-02-2020
20111 powi43 20-01-2020 16:35:1607-01-2020   20-01-2020
20999powi43 20-01-2020 16:35:1607-01-2020   20-01-2020
20111   powi43 16-01-2020 12:35:16   07-01-2020   20-01-2020
20212   powi43 18-01-2020 12:35:16   07-01-2020   20-01-2020

 

 

i just want to show 20212 ...can anyone help please?

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    Please new a measure:

     

    Measure = 
VAR _sprint_start = MAX('Table'[sprint start])
VAR _date_changed = MAX('Table'[datechange])
VAR  _days = _date_changed - _sprint_start
VAR _filter = IF(_days>1,1)
RETURN
_filter

     

    You can use it as a filter for table visuals or use it to get the number of conditions met.

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly -- How to provide sample data

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    New a calculated column:

    the number of workitems = 
VAR _date = 'Table'[sprint start] + 1
VAR _num = CALCULATE(COUNT('Table'[workitems]),FILTER('Table','Table'[sprint start]>=_date))
RETURN
_num

    Please check if it is what you want:

    If you need to de-duplicate the result you can replace COUNT() with DISTINCTCOUNT(). If I have misunderstood your requirements, please post the expected output. This would be very helpful and much appreciated!

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly -- How to provide sample data

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      it should just show the row with workitems with id- 20212 as it is added on the second the day of the sprint start

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi Anonymous ,

         

        Please new a measure:

         

        Measure = 
VAR _sprint_start = MAX('Table'[sprint start])
VAR _date_changed = MAX('Table'[datechange])
VAR  _days = _date_changed - _sprint_start
VAR _filter = IF(_days>1,1)
RETURN
_filter

         

        You can use it as a filter for table visuals or use it to get the number of conditions met.

        Best Regards,
        Gao
        Community Support Team

         

        If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

        How to get your questions answered quickly -- How to provide sample data