Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
tushars22
Frequent Visitor

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:

 

IDStatusCreation DateLast Modified
44441Fixed5-Feb-206-Feb-20
44442Reopened5-Feb-206-Feb-20
44443New4-Feb-206-Feb-20
44444New5-Feb-207-Feb-20
44445New5-Feb-207-Feb-20
44446Reject4-Feb-207-Feb-20
44447Deferred4-Feb-207-Feb-20
44448Fixed12-Feb-2013-Feb-20
44449Fixed13-Feb-2013-Feb-20
44450Retest13-Feb-2014-Feb-20
44451New12-Feb-2012-Feb-20
44452Reject12-Feb-2013-Feb-20
44453Reject12-Feb-2013-Feb-20
44454Retest14-Feb-2014-Feb-20
44455Deferred14-Feb-2014-Feb-20
44456Fixed14-Feb-2015-Feb-20

 

Need a Graph to show Weekly Trend of Incoming Vs Handled.

 

Weekly Incoming  TrendNew based on Creation Date
Weekly Handled TrendLast Modified date and If Status is "Fixed", "Reject", "Deferred", "Retest"
1 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:

15.png

 

BTW, I also attached the sample file below.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

5 REPLIES 5
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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 @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:

15.png

 

BTW, I also attached the sample file below.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Thank you @v-shex-msft 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:

 

 StatusCreation DateLast ModifiedDate FixedDate RejectedDate Deferred
44441Fixed5-Feb-206-Feb-206-Feb-20  
44442Reopened5-Feb-206-Feb-20   
44443New4-Feb-206-Feb-20   
44444New5-Feb-207-Feb-20   
44445New5-Feb-207-Feb-20   
44446Reject4-Feb-207-Feb-20 7-Feb-20 
44447Deferred4-Feb-207-Feb-20  7-Feb-20
44448Fixed12-Feb-2013-Feb-2013-Feb-20  
44449Fixed13-Feb-2013-Feb-2013-Feb-20  
44450Retest13-Feb-2014-Feb-2014-Feb-20  
44451New12-Feb-2012-Feb-20   
44452Reject12-Feb-2013-Feb-20 7-Feb-20 
44453Reject12-Feb-2013-Feb-20 7-Feb-20 
44454Retest14-Feb-2014-Feb-2014-Feb-20  
44455Deferred14-Feb-2014-Feb-20  14-Feb-20
44456Fixed14-Feb-2015-Feb-2015-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
Super User
Super User

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/

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.