calculate n days sales for locations with different start dates

[abhishekc1]

Hi All,

Could you please help me out with this? My data is setup in the following way:

a) Data table - contains order date, location name, customer id,  product name, quantity, amount and order id

b) Calendar table - date table that is connected to the data table 

c) Locationkey - contains locations and their respective start dates

for example: 

Location	Start Date
Loc 1	9/1/2017
Loc 2	11/1/2017
Loc 3	2/2/2018
Loc 4	3/5/2018
Loc 5	7/6/2018
Loc 6	10/10/2018
Loc 7	12/22/2018
Loc 8	2/2/2019
Loc 9	7/4/2019
Loc 10	17/6/2019

The expected result that would help me further would be:

Location

1st week sales

2nd week sales

3rd week sales

4th week sales

5th week sales

6th week sales

7th week sales

8th week sales

9th week sales

10th week sales

11th week sales

nth week sales

Loc 1

Loc 2

Loc 3

Loc 4

Loc 5

Loc 6

Loc 7

Loc 8

Loc 9

Loc 10

1st week sales would be sales for the first week after the launch date.

Thanks 

[v-frfei-msft · Accepted solution]

Hi @abhishekc1 ,

Please check the following steps as below.

1. To creat a date table and insert a calculated column in it.

DATE = CALENDARAUTO()

Column = WEEKNUM('DATE'[Date]) & "WEEK" & YEAR('DATE'[Date])

2. Create relationship between date table and Locationkey table.

3. Then we can create a matrix as below.