Count of Individual KPI and Line chart by Month

[sunny27]

Hello All,

Below is my Data

Student ID	Status	Created Date
98	Pending	12/1/2015
98	Approved	1/1/2016
99	Pending	12/1/2015
99	Approved	12/15/2015
99	Closed	1/15/2016
100	Pending	1/1/2016
100	Approved	5/10/2016
100	Closed	11/10/2016
101	Pending	1/1/2016
103	Pending	2/1/2016
104	Pending	3/1/2016
105	Pending	4/1/2016
105	Approved	9/14/2016
115	Pending	5/2/2016
115	Approved	5/14/2016
115	Closed	5/25/2016
116	Pending	5/28/2016
117	Pending	5/29/2016
118	Pending	5/30/2016
119	Pending	5/31/2016

I am trying to display 

Output For KPI's

Pending  =7(Max(Createddate,Filter(tbl,tbl[Status] = "Pending")))

Approved  = 3(Max(Createddate,Filter(tbl,tbl[Status] = "Approved")))

Output for Trend

I need to display the trend --> Status by Month-Year.

The statuses are taken based on last day of the month. For example: If Student 115 has status Pending,Approved,Closed in same month, then we only should count the final status Closed and ignore other statuses.

115	Pending	5/2/2016
115	Approved	5/14/2016
115	Closed	5/25/2016

Also, if the status changed on different months, then we need to display the status until the change occurs. Eg: 

Student ID	Status	Created Date
100	Pending	1/1/2016
100	Approved	5/10/2016
100	Closed	11/10/2016

In this case, the status Pending should be displayed from Jan to April 2016. Status Approved should be from May 2016 to Oct 2016 and Status Closed should be from Nov 2016 onwards until next change.

Is there a simple way to do this? I am trying to SUMMARIZE and Group By but it's not working. I also checked CALCULATEDTABLE to created a new table but the trend is off totally.

Thanks 🙂

[v-jiascu-msft]

Hi @sunny27,

I have a few questions. 

1. For example, Pending  =7(Max(Createddate,Filter(tbl,tbl[Status] = "Pending"))) = 7 * 12/1/2015. What is it? How can we put it in a visual?

2. Do you mean every student has all the status for all the year-month? For example, only show 6 months data here.

98 2015-01 Pending

98 2015-02 Pending

98 2015-03 Approved

98 2015-04 Pending

98 2015-05 Pending

98 2015-06 Pending

99 2015-01 Pending

99 2015-02 Pending

99 2015-03 Approved

99 2015-04 Pending

99 2015-05 Pending

99 2015-06 Pending

If so, I'm afraid we can't put them in a line visual.

Best Regards,

Dale