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,
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
Thank you Xioaxin, BUt i cannot find your solution. The hyperlink you gave takes me to the forum where you provide a solution but on clicking on Go to SOlution, nothing happens.
Can you check and share me the right link please ?
Thanks.
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