Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to Solution.
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]) ) )
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]) ) )
User | Count |
---|---|
97 | |
87 | |
77 | |
67 | |
63 |
User | Count |
---|---|
112 | |
96 | |
95 | |
67 | |
65 |