Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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.
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.
User | Count |
---|---|
139 | |
113 | |
103 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |