Create Custom Time Frames from my weekly data

[Anonymous]

Hello All,

I have a data set that contains weekly data by product. I want to transform this data into Latest 4, 13, 26 or 52 week time frames that the user can select via slicers. Imagine my table below has 52 weeks of data with Current Year and Previous Year in 2 columns.

I want to transform the latest 4 weeks Current Year into a column, 4 weeks Previous Year into another column, 13 weeks Current Year into another column and so forth.

Thoughts?

W/E Date	Product	Sales - Current Year	Sales - Previous Year	Distribution Center
05/30/20	Product 1	$100.00	$75.00	Atlanta
05/30/20	Product 2	$43.00	$26.00	Atlanta
05/30/20	Product 1	$267.00	$183.00	Denver
05/23/20	Product 1	$86.00	$92.00	Atlanta
05/23/20	Product 2	$115.00	$64.00	Atlanta
05/23/20	Product 2	$291.00	$306.00	Denver

[v-xuding-msft]

Hi @Anonymous ,

I create a sample using the function of DATEADD. Please reference it to have a try:

• Create a table

Date = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))

And create a relationship between it and your table.

• Create measures

Last 4 Weeks this year = CALCULATE(SUM('Table'[Sales]),DATEADD('Date'[Date],-28,DAY))

Last 4 weeks last year = CALCULATE([Last 4 Weeks this year],DATEADD('Date'[Date],-1,YEAR))

Last 13 Weeks = CALCULATE(SUM('Table'[Sales]),DATEADD('Date'[Date],-91,DAY))

For calculating the values of last 13 weeks, it will get the data of last year if the week is less than 13.

Please download sample below to have a try.

[Anonymous]

@v-xuding-msft 

Thank you! Another question.....would 26 weeks be -179 and 52 weeks be -365?

[v-xuding-msft]

Hi @Anonymous ,

Yes.  There is not a week selection for interval in the function. So it is more exact to use day to get the results than month.

( the numbers of week multiply 7) 

eg:

last 4 week:   -4*7 =-28
last 13 week:   -13*7 =-91
last 26 week:   -26*7 =-182
last 52 week:   -52*7 =-364

If you want to use month or year, please try this:

Last 4 Weeks this year = CALCULATE(SUM('Table'[Sales]),DATEADD('Date'[Date],-1,MONTH))

Last 52 Weeks this year = CALCULATE(SUM('Table'[Sales]),DATEADD('Date'[Date],-1,YEAR))

[v-xuding-msft · Accepted solution]

Hi @Anonymous ,

Could you tell me if your problem has been solved? If it is, kindly mark the helpful answer as solution if you feel that makes sense. Welcome to share your own solution. More people will benefit from the thread.

[amitchandak]

@Anonymous , Refer to my blog on Week.

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123

You can use week rank. Now Can find today's Week Rank and use that create week buckets in date table or use rolling formula's

I have given formula in a comment for year behind week