Forum Discussion

bmk's avatar
bmk
Helper II
3 years ago

Rolling Weekly Metrics : Line Chart

Hello,

 

Based on the table below I wish to understand how to go about developing a visual where an aggregation around a column, say 'Seconds' in this case is to be displayed for each country every 7 days keeping in mind the different start dates of each country.

Objective : Line Chart with Weeks and Location on the X-axis and Sum(seconds) or Average(seconds) on the Y-Axis. We are peforming a weekly comparison of the total time of each country.

 

To elaborate in words :

Week 1 : Germany : 177.35 total seconds

Week 1 : US : 141.91 total seconds

Week 1 : UK : 772.81 total seconds

Week 2 : Germany : 565.9 total seconds
Week 2 : US : 476.06 total seconds
Week 2: UK : 725.74 total seconds

Week 3 : Germany : 281.26 total seconds

 

Any help is appreciated.

 

 




Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    bmk , You can join the date with date of date table and you can have week column in date table

     

    Have these new columns in Date Table, Week Rank is Important in Date/Week Table
    Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
    Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
    WeekDay = weekday([Date],2)
    Have these new columns in Date Table, Week Rank is Important in Date/Week Table

    Week Rank = RANKX('Date','Date'[Week Start date],,ASC,Dense)
    OR
    Week Rank = RANKX('Date','Date'[Year Week],,ASC,Dense) //YYYYWW format

     

    This Week = CALCULATE(sum('Table'[Qty]))

    or

    This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
    Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

     

    Avg Last 4 weeks = CALCULATE(Averagex(Values('Date'[Week Rank]), [orders ]) , FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-4 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

     

     

    • bmk's avatar
      bmk
      Helper II
      3 years ago

      Hello amitchandak , I apologize for the incovenience, would you mind showing me the solution on the attached PBIX? I am using a Date Table, but the measures are not picking up columns for some reason.

      I also wanted to understand the solution overall to make sure the metrics are picked up as per the first date of each location and then their weekly calculcations.

       

      I am unable to attach the .pbix file here. Please see the link : https://drive.google.com/file/d/1KPF23HdLIQY2awLES2XzrrFpPiy-BuWB/view?usp=share_link

    • bmk's avatar
      bmk
      Helper II
      3 years ago

      I was able to get the date columns in, but I am unable to understand how to represent the metrics (total seconds) based on start date of a location on a weekly basis.