Rolling Weekly Metrics : Line Chart

[bmk]

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.

[amitchandak]

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

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.

[bmk]

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