Create a count of id by date to match a date in a created table

[Fair-UL]

Hi all,

I am trying to create a graph that trends over time count of tasks. Tasks have two status type. I need to find a count of 3 groups

id	status	create date	complete date
1	complete	8/10/2019	8/12/2019
2	incomplete	8/10/2019
3	complete	8/10/2019	8/13/2019
4	incomplete	8/10/2019
5	incomplete	8/11/2019
6	incomplete	8/12/2019
9	complete	8/15/2019	8/17/2019
10	incomplete	8/15/2019
11	incomplete	8/15/2019

 blank completion date means task is incomplete

data shows an example of the problem. I want to able to create something like this:

Date	Completed	Created	In-process
8/10/2019	0	2	2
8/11/2019		1	3
8/12/2019	0	1	4
8/15/2019	1	2	5

where In-process (today)= In-Process (yesterday)+Created (Today)- Completed (Today)

I created two columns that gets id if completed and returns null otherwise. the other one works vice versa.

I created a table that contains a date column:

now my issue is how to pull distict count of ide to align with date in the new table. I need this table to be able to graph the trend over time. How do I pursue with this DAX statement? 

[v-juanli-msft · Accepted solution]

Hi @Fair-UL 

Create a calendar table

calendar = CALENDARAUTO()

Create measures

Created = CALCULATE(COUNT('Table'[id]),FILTER(ALL('Table'),'Table'[create date]=MAX('calendar'[Date])))

completed = CALCULATE(COUNT('Table'[id]),FILTER(ALL('Table'),'Table'[complete date]=MAX('calendar'[Date])))

in-process =
IF (
    [Created] <> BLANK ()
        || [completed] <> BLANK (),
    SUMX (
        FILTER ( ALL ( 'calendar' ), 'calendar'[Date] <= MAX ( 'calendar'[Date] ) ),
        [Created]
    )
)
    - IF (
        [Created] <> BLANK ()
            || [completed] <> BLANK (),
        SUMX (
            FILTER ( ALL ( 'calendar' ), 'calendar'[Date] <= MAX ( 'calendar'[Date] ) ),
            [completed]
        )
    )

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Fair-UL]

Hi Maggie,

Thank you so much for the sloution. The equations worked well except in 

Created = CALCULATE(COUNT('Table'[id]),FILTER(ALL('Table'),'Table'[create date]=MAX('calendar'[Date])))

It was missing [Date] after 'Table' [create date]

Created = CALCULATE(COUNT('Table'[id]),FILTER(ALL('Table'),'Table'[create date].[Date]=MAX('calendar'[Date])))

I had to transform all comeplete and create date to fate format and all be the same mm/dd/yy

Now I am facing the issue of this table/ chart (I turned it into a line chart) is static. It does not respond to slicers on the page. 

I understand that calculated measures overrides slicers. How can we resolve this issue?

TIA

[v-juanli-msft]

Hi @Fair-UL 

Could you show which id is counted in the table?

Date	Completed	Created	In-process
8/10/2019	0	2	2
8/11/2019		1	3
8/12/2019	0	1	4
8/15/2019	1	2	5

For example, based on my understanding,

Date	Completed	Created	In-process
8/10/2019	0	id :2,3	id: 1,2
8/11/2019		id:5	3
8/12/2019	0	id:6	4
8/15/2019	1	id:10,11	5

But i don't know how to get the rest counts?

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-juanli-msft]

Hi @Fair-UL 

Could you show which id is counted in the table?

Date	Completed	Created	In-process
8/10/2019	0	2	2
8/11/2019		1	3
8/12/2019	0	1	4
8/15/2019	1	2	5

For example, based on my understanding,

Date	Completed	Created	In-process
8/10/2019	0	id :2,3	id: 1,2
8/11/2019		id:5	3
8/12/2019	0	id:6	4
8/15/2019	1	id:10,11	5

But i don't know how to get the rest counts?

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Fair-UL]

Thank you Maggie for asking this question.

The In-process column is calculated based on the following equation: In-process (current day)= In-Process (last day calculated)+Created (current day)- Completed (current day)

dates are not consecutive as there are weekends

 so the table with the id numbers is as follows: (the first one i posted is not what I want as I need to count any project created on a certain day by creation day). if the status changed to Complete later (few days later), then I want to count this as completed on that completion date. Here is the correct table with what I need 

Date	Completed	Created	In-process
8/10/2019	0	4 (id:1,2,3,4)	4
8/11/2019	0	1 (id:5)	5
8/12/2019	1 (id:1)	1 (id:6)	5
8/13/2019	1 (id:3)	0	4
8/15/2019	0	3 (id:9,10,11)	7
8/17/2019	1 (id:9)	0	6

so on 8/10/19, In-process= 4 as only 4 created and 0 completed on that date.

on 8/11/19, in-process= 4 (from previous date)+1(created on 8/11)-0 (as non completed on 8/11)=5

on 8/12/19, in-process=5 (from previous date)+1(created on 8/12)-1 (as one completed on 8/11)=5

I hope this clarifies the problem

Thank you