cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sunny27 Regular Visitor
Regular Visitor

Count of Individual KPI and Line chart by Month

Hello All,

 

Below is my Data

 

Student IDStatusCreated Date
98Pending12/1/2015
98Approved1/1/2016
99Pending12/1/2015
99Approved12/15/2015
99Closed1/15/2016
100Pending1/1/2016
100Approved5/10/2016
100Closed11/10/2016
101Pending1/1/2016
103Pending2/1/2016
104Pending3/1/2016
105Pending4/1/2016
105Approved9/14/2016
115Pending5/2/2016
115Approved5/14/2016
115Closed5/25/2016
116Pending5/28/2016
117Pending5/29/2016
118Pending5/30/2016
119Pending5/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.

 

115Pending5/2/2016
115Approved5/14/2016
115Closed5/25/2016

 

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

Student IDStatusCreated Date
100Pending1/1/2016
100Approved5/10/2016
100Closed11/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 Smiley Happy

 

1 REPLY 1
Community Support Team
Community Support Team

Re: Count of Individual KPI and Line chart by Month

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

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