Forum Discussion
Cumulative Distinct Count and Slicer
Hello,
I am running circles with this:
Need to do a cumulative distinc count ( task ) So that I can play two cumulative line charts.
Line A - Cumulative Task by start date ( started tasks)
Line B - Cumulative task by finish date (closed tasks)
After that I must be able to apply slicers and get Lines A and B based on slicer settings (by department and employee).
I have a calendar table which is link to this table by Finish Date (I got other reports taht need this link).
thanks!
| Task | Department | Employee | Start date | Finish Date |
| 1 | A | Jay | 01/01/2017 | 30/03/2017 |
| 2 | A | John | 10/01/2017 | 15/01/2017 |
| 2 | B | Katy | 10/01/2017 | 15/01/2017 |
| 2 | B | Vivi | 10/01/2017 | 15/01/2017 |
| 5 | C | Bob | 05/02/2017 | 15/02/2017 |
| 6 | A | Jay | 28/02/2017 | 15/03/2017 |
| 7 | C | Bob | 30/03/2017 | 15/06/2017 |
| 7 | B | Vivi | 30/03/2017 | 15/06/2017 |
| 9 | A | John | 04/04/2017 | 15/06/2017 |
| 10 | A | John | 01/05/2017 | 01/04/2017 |
| 10 | A | Jay | 01/05/2017 | 01/04/2017 |
| 12 | B | Katy | 01/06/2017 | 10/06/2017 |
| 13 | B | Vivi | 15/06/2017 | 10/06/2017 |
| 14 | B | Vivi | 15/05/2017 | 10/06/2017 |
| 14 | C | Bob | 15/05/2017 | 10/06/2017 |
| 14 | C | Cris | 15/05/2017 | 10/06/2017 |
Hi Anonymous
I think these calculated measures might be close...
Cumulative Task by Start Date = CALCULATE( DISTINCTCOUNT('Table1'[Task]), FILTER( ALLSELECTED('Table1'), 'Table1'[Start date]<=MAX('Dates'[Date]) ) )and
Cumulative Task by Finish Date = CALCULATE( DISTINCTCOUNT('Table1'[Task]), FILTER( ALLSELECTED('Table1'), 'Table1'[Finish Date]<=MAX('Dates'[Date]) ) )
2 Replies
- Phil_Seamark
Microsoft Employee
Hi Anonymous
I think these calculated measures might be close...
Cumulative Task by Start Date = CALCULATE( DISTINCTCOUNT('Table1'[Task]), FILTER( ALLSELECTED('Table1'), 'Table1'[Start date]<=MAX('Dates'[Date]) ) )and
Cumulative Task by Finish Date = CALCULATE( DISTINCTCOUNT('Table1'[Task]), FILTER( ALLSELECTED('Table1'), 'Table1'[Finish Date]<=MAX('Dates'[Date]) ) )