Forum Discussion

sunny27's avatar
sunny27
Frequent Visitor
8 years ago
Solved

Display trend as a line chart Time intelligence

Hi,

 

Here is my data: -

 

Student IDStatusCreated Date
10011/1/2016
10025/10/2016
100311/10/2016
10111/1/2016
10312/1/2016
10413/1/2016
10514/1/2016
10529/14/2016
11515/2/2016
11525/14/2016
11535/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.

 

11515/2/2016
11525/14/2016
11535/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
10011/1/2016
10025/10/2016
100311/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.

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-juanli-msft's avatar
    v-juanli-msft
    8 years ago

    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

3 Replies