Forum Discussion
Creating Trend graphs for Weekly Data
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
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
Thank you Anonymous for below solution. It does help me understand how to create the needed trend.
However my requirement has changed as team added more columns int he report and was hoping you can provide some guidance here
My new Table:
| Status | Creation Date | Last Modified | Date Fixed | Date Rejected | Date Deferred | |
| 44441 | Fixed | 5-Feb-20 | 6-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 | 7-Feb-20 | ||
| 44447 | Deferred | 4-Feb-20 | 7-Feb-20 | 7-Feb-20 | ||
| 44448 | Fixed | 12-Feb-20 | 13-Feb-20 | 13-Feb-20 | ||
| 44449 | Fixed | 13-Feb-20 | 13-Feb-20 | 13-Feb-20 | ||
| 44450 | Retest | 13-Feb-20 | 14-Feb-20 | 14-Feb-20 | ||
| 44451 | New | 12-Feb-20 | 12-Feb-20 | |||
| 44452 | Reject | 12-Feb-20 | 13-Feb-20 | 7-Feb-20 | ||
| 44453 | Reject | 12-Feb-20 | 13-Feb-20 | 7-Feb-20 | ||
| 44454 | Retest | 14-Feb-20 | 14-Feb-20 | 14-Feb-20 | ||
| 44455 | Deferred | 14-Feb-20 | 14-Feb-20 | 14-Feb-20 | ||
| 44456 | Fixed | 14-Feb-20 | 15-Feb-20 | 15-Feb-20 |
What I need:
Create a Measure or Calculated Column which ever works fine as per below need
Status = Fixed or Rejected or Deferred
Count of all IDs with above status filtered on a day to day basis