Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
Thanks so much. That works! Just in case I have questions, I will post here.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |