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 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...
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

v-juanli-msft's avatar
v-juanli-msft
Icon for Community Support rankCommunity Support
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

  • sunny27's avatar
    sunny27
    Frequent Visitor
    8 years ago

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