scope increase and decrease

[Anonymous]

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:16	07-01-2020	20-01-2020
20999	powi43	20-01-2020 16:35:16	07-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?

[Ashish_Mathur]

Hi,

Please show the exact result that you are expecting.

[Anonymous]

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

[Ashish_Mathur]

Hi,

Download my PBI file from here.

Hope this helps.

[Anonymous · Accepted solution]

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

[Anonymous]

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