sum of rolling 12 months sales

[Anonymous]

Hi,

I am trying to achieve a rolling 12 months sum  of sales revenue.

I have two tables:

These two tables are joined based on date field.

Table-1

Weeknumber	Date	MonthNumber	year
27	7/6/2019	7	2019
28	7/14/2019	7	2019

Table -2

Sales Revenue	Date
3000	7/6/2019
4000	7/14/2019

I now want to have a rolling 12 months sales based on the week number:

for ex. 2019 week 27 to 2020 week 26

I tried this measure but i get same value as sales rvenue for all the rows ,

Measure-1 = Calculate(Sum(Table-1[Sales Revenue]),

DATESINPERIOD(Table-2[date],max(Table-2[date]),-12,MONTH))

Any help how to get this 52 weeks of rolling data 

Thanks,

Tejaswi

[amitchandak]

@Anonymous 

Refer to this file. It uses Week rank to deal with Week , prior week rolling week

https://www.dropbox.com/s/bu47m5ek7sr9v8t/sales_analytics_v2.pbix?dl=0

Typically same week year is 364 Days behind

[Anonymous]

HI @amitchandak ,

Thanks for your reply.

I am going thru your file and have follinwg questions

1, Is Weeknumber field same as week rank you mentioedn?

2. and are u using the rolling 1 measure to get this result?

Appreaciate your reply.

Thanks,

Tejaswi

[amitchandak]

@Anonymous 

In the date table; refer to Week Rank.

And refer to Page 1 and week wise pages

[Anonymous]

@amitchandak ,

There is not field as week rank in the Date Table.

neither there is any week data in the pages..

Am I missing somethign? 

Thanks,

Tejaswi

[amitchandak · Accepted solution]

@Anonymous 

Attaching the file

[Anonymous]

Hi @amitchandak ,

Since my date is not in conituous format I cannot use the DATEADD function , as it gives me an error.

What alternative could be possible? can i use datesinperiod?

 since i am trying to go back 52 weeks behind to get the last 12 months rolling week data.

Thanks,

Tejaswi

[amitchandak]

@Anonymous , you should create a date calendar and join with that. And there you can use dateadd.

The other way is

Rolling 12 =
var _max = maxx('Sales','Sales'[Date])
var _min = date(year(_max),month(_max),day(_max)-364)
//Or
//var _min = date(year(_max)-1,month(_max),day(_max))

return
CALCULATE(SUM('Sales'[Sales]), FILTER(all('Sales'), 'Sales'[Date]<=_max && 'Sales'[Date]>=_min))

[Anonymous]

HI @amitchandak ,

I do have a date data in different table and it is join to my sales Table, However it is not continuous.

Also i am gettign correct value when i try with your formula week rank , my last week and last 12 weeks data comes correct with your formula , However, last 52 weeks data is incorrect.

I tried the formula which you just gave but getting different values..

Thanks,'Tejaswi

[Anonymous]

Hi @amitchandak ,

I modified your last 12 weeks measure to show the 52 weeks data and it worked for me.

Thanks a lot for your help!

Thanks,

Tejaswi

[Anonymous]

Thanks @amitchandak ,

This file was missing.. I will go thru the formulas and logic and will get back to you for any question.

Thanks,

Tejaswi