Line chart with item status

[Anonymous]

Hello all,

I hope you can help me. I would like to have a line chart that shows the status of an item. An item can be open or closed. Open items can be overdue. My fact table looks like the one below.

My goal is to have a line chart with two lines. One that shows the # of open items and one that shows the # of open items that are overdue.

Any advice on how to do this would be much appreciated, thanks!

ItemNo	CreateDate	DueDate	CloseDate
1	1-1-2020	1-4-2020	31-3-2020
2	1-1-2020	1-4-2020	31-3-2020
3	1-1-2020	1-4-2020	1-6-2020
4	1-1-2020	1-4-2020	1-6-2020
5	1-1-2020	1-5-2020	1-6-2020
6	1-1-2020	1-6-2020	31-5-2020
7	1-1-2020	31-12-2020
8	1-1-2020	31-12-2020
9	1-1-2020	31-12-2020
10	15-3-2020	30-4-2020	31-7-2020
11	15-3-2020	30-4-2020	31-7-2020
12	15-3-2020	30-4-2020	31-7-2020
13	15-3-2020	30-4-2020	31-7-2020
14	15-3-2020	30-4-2020	31-7-2020
15	15-3-2020	30-4-2020
16	15-3-2020	30-4-2020
17	15-3-2020	30-4-2020
18	15-3-2020	30-4-2021	31-3-2020
19	15-3-2020	30-4-2021
20	15-3-2020	30-4-2021
21	15-3-2020	30-4-2021	31-12-2020
22	15-3-2020	30-4-2021
23	15-3-2020	30-4-2021
24	15-3-2020	31-12-2020
25	31-5-2020	31-8-2020	30-11-2020
26	31-5-2020	31-8-2020	30-11-2020
27	31-5-2020	31-8-2020	30-11-2020
28	31-5-2020	31-8-2020	30-11-2020
29	31-5-2020	31-8-2020	30-11-2020
30	31-5-2020	31-8-2020
31	31-5-2020	31-8-2020
32	31-5-2020	31-8-2020
33	31-5-2020	31-8-2020
34	31-5-2020	31-8-2020	15-7-2020
35	31-5-2020	31-8-2020	15-7-2020
36	31-5-2020	31-12-2020
37	31-5-2020	31-12-2020	30-11-2020
38	31-5-2020	31-12-2020
39	31-5-2020	31-12-2020	1-2-2021
40	31-5-2020	31-12-2021
41	31-5-2020	31-12-2021
42	15-6-2020	31-10-2020	31-8-2020
43	15-6-2020	31-10-2020	31-8-2020
44	15-6-2020	31-10-2020	31-12-2020
45	15-6-2020	31-10-2020	31-12-2020
46	15-6-2020	31-10-2020	31-12-2020
47	15-6-2020	31-10-2020
48	15-6-2020	31-10-2020
49	15-6-2020	31-10-2020
50	15-6-2020	31-10-2020

[Ashish_Mathur]

Hi,

What's the calculation for the "number of open items" and "number of open items that are overdue"? 

[Anonymous]

@Ashish_Mathur , thank you for you message.

# of Open Items = # ItemNo where ItemTable[CloseDate] > Calendar[Date]

# of Overdue Items = # Open Items where ItemTable[DueDate] < Calendar[Date]

Kind regards

[Anonymous]

Hi @Anonymous ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

Best Regards

[Anonymous]

@Anonymous , thank you very much! It is almost what I would like to achieve, but for now the calculation doesn't work. For example, if you look at the items with a create date of 1-1-2020 (1 January 2020, dd-mm-yyyy):

ItemNo CreateDate DueDate CloseDate
1 1-1-2020 1-4-2020 31-3-2020
2 1-1-2020 1-4-2020 31-3-2020
3 1-1-2020 1-4-2020 1-6-2020
4 1-1-2020 1-4-2020 1-6-2020
5 1-1-2020 1-5-2020 1-6-2020
6 1-1-2020 1-6-2020 31-5-2020
7 1-1-2020 31-12-2020
8 1-1-2020 31-12-2020
9 1-1-2020 31-12-2020

On 1-1-2020 there are 9 open items. 2 are closed on 31-3-2020, so on that date there are 7 open items. On 1-4-2020 4 items are overdue, of which 2 are closed. So on 1-4-2020 there are 7 open items, of which 2 are overdue. And on 1-5-2020 there are 7 open items, of which 3 are overdue.

Thanks again,

Frank

[Anonymous · Accepted solution]

Hi @Anonymous ,

I updated the sample pbix file(see attachment), please check whether that is what you want.

Best Regards

[Anonymous]

@Anonymous , thank you so much. This is exactly what I was looking for!

[amitchandak]

@Anonymous ,  For Open Item, refer this blog

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

For overdue  add additional condition && Table[DueDate] >max(Date[Date])

[Anonymous]

@amitchandak , thank you very much for sharing. So far, I didn't make it work, but I need to put some more time into it.