cancel
Showing results for
Did you mean:
Frequent Visitor

## calculate n days sales for locations with different start dates

Hi All,

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

1 ACCEPTED SOLUTION
Community Support

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.

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Community Support

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.

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Announcements