Creating Trend graphs for Weekly Data

[tushars22]

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"

[Anonymous]

Hi @tushars22,

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

[tushars22]

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.

[Anonymous · Accepted solution]

Hi @tushars22,

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

[tushars22]

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

[amitchandak]

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/