Forum Discussion
Creating Trend graphs for Weekly Data
Hi, I am new in Power BI and trying to create a Weekly trand Graph for our IT team .. Sample data is as follows:
| ID | Status | Creation Date | Last Modified |
| 44441 | Fixed | 5-Feb-20 | 6-Feb-20 |
| 44442 | Reopened | 5-Feb-20 | 6-Feb-20 |
| 44443 | New | 4-Feb-20 | 6-Feb-20 |
| 44444 | New | 5-Feb-20 | 7-Feb-20 |
| 44445 | New | 5-Feb-20 | 7-Feb-20 |
| 44446 | Reject | 4-Feb-20 | 7-Feb-20 |
| 44447 | Deferred | 4-Feb-20 | 7-Feb-20 |
| 44448 | Fixed | 12-Feb-20 | 13-Feb-20 |
| 44449 | Fixed | 13-Feb-20 | 13-Feb-20 |
| 44450 | Retest | 13-Feb-20 | 14-Feb-20 |
| 44451 | New | 12-Feb-20 | 12-Feb-20 |
| 44452 | Reject | 12-Feb-20 | 13-Feb-20 |
| 44453 | Reject | 12-Feb-20 | 13-Feb-20 |
| 44454 | Retest | 14-Feb-20 | 14-Feb-20 |
| 44455 | Deferred | 14-Feb-20 | 14-Feb-20 |
| 44456 | Fixed | 14-Feb-20 | 15-Feb-20 |
Need a Graph to show Weekly Trend of Incoming Vs Handled.
| Weekly Incoming Trend | New based on Creation Date |
| Weekly Handled Trend | Last Modified date and If Status is "Fixed", "Reject", "Deferred", "Retest" |
Hi Anonymous,
You can check the following steps if they suitable for your requirements.
Steps:
1. Create a expand table based on id and its date ranges.
Expand = SELECTCOLUMNS ( FILTER ( CROSSJOIN ( Test, CALENDAR ( MIN ( Test[Creation Date] ), MAX ( Test[Last Modified] ) ) ), [Date] >= [Creation Date] && [Date] <= [Last Modified] ), "ID", [ID], "Date", [Date] )2. Build a relationship from expand table to original table based on id field with 'both' direction mode.
3. Create a line chart with expand table date as axis, original table 'status' as legend and 'id' as value. (If you need a trend line, you can enable it in the analysis panel trend option.)
4. Create a slicer with status fields to choose display records.
Snapshot:
BTW, I also attached the sample file below.
Regards,
Xiaoxin Sheng
5 Replies
You can create measures like this
calculate(count(table[ID]))
calculate(count(table[ID]), table[status] in {"Fixed", "Reject", "Deferred", "Retest"})For week like this week , last week and calendar for week wise refer : https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/Hi Anonymous,
Perhaps you can create a calculated table to extract and expand your date fields and link to the original table. After these, you can directly use them on your visual to analytics records.
Spread revenue across period based on start and end date, slice and dase this using different dates
Regards,Xiaoxin Sheng
Hi Anonymous,
You can check the following steps if they suitable for your requirements.
Steps:
1. Create a expand table based on id and its date ranges.
Expand = SELECTCOLUMNS ( FILTER ( CROSSJOIN ( Test, CALENDAR ( MIN ( Test[Creation Date] ), MAX ( Test[Last Modified] ) ) ), [Date] >= [Creation Date] && [Date] <= [Last Modified] ), "ID", [ID], "Date", [Date] )2. Build a relationship from expand table to original table based on id field with 'both' direction mode.
3. Create a line chart with expand table date as axis, original table 'status' as legend and 'id' as value. (If you need a trend line, you can enable it in the analysis panel trend option.)
4. Create a slicer with status fields to choose display records.
Snapshot:
BTW, I also attached the sample file below.
Regards,
Xiaoxin Sheng
