Display trend as a line chart Time intelligence

[sunny27]

Hi,

Here is my data: -

Student ID	Status	Created Date
100	1	1/1/2016
100	2	5/10/2016
100	3	11/10/2016
101	1	1/1/2016
103	1	2/1/2016
104	1	3/1/2016
105	1	4/1/2016
105	2	9/14/2016
115	1	5/2/2016
115	2	5/14/2016
115	3	5/25/2016

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 1,2,3 in same month, then we only should count the final status "3" and ignore other statuses.

115	1	5/2/2016
115	2	5/14/2016
115	3	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	1	1/1/2016
100	2	5/10/2016
100	3	11/10/2016

In this case, the status 1 should be displayed from Jan to April 2016. Status 2 should be from May 2016 to Oct 2016 and Status 3 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.

[sunny27]

https://community.powerbi.com/t5/Desktop/Display-trend-as-a-line-chart-Time-intelligence/m-p/508568#...

[v-juanli-msft · Accepted solution]

Hi @sunny27

It is possible to count the final status "3" and ignore other statuses when there are different status in same month.

Create acalculated columns

month = MONTH([Created Date])

max per month = CALCULATE(MAX([Created Date]),FILTER(ALL(Table1),[Student ID]=EARLIER(Table1[Student ID])&&[month]=EARLIER(Table1[month])))

Then create a new table

Table 2 = SUMMARIZE(FILTER(ALL(Table1),[Created Date]=[max per month]),[Student ID],Table1[Status],Table1[Created Date])

Best Regards

Maggie

[sunny27]

Thanks so much. That works! Just in case I have questions, I will post here.