Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello, everybody. Hope all the best for you.
I have a small request. I have my sales data registered weekly by department and store. I use a date from within the week (from monday to friday) as my date column in my table.
I want to create a sequential column for each week by store. For example, in the week 1, all the rows correspondent to departments in store A should have the value of 1, and for the week 2 they should have the value of 2.
Here is an example of my data:
Department | Store | Date | Unit Sales |
1 | A | 10/02/2023 | 181 |
2 | A | 10/02/2023 | 53 |
3 | A | 10/02/2023 | 54 |
4 | A | 10/02/2023 | 66 |
1 | B | 09/02/2023 | 112 |
2 | B | 09/02/2023 | 145 |
3 | B | 09/02/2023 | 177 |
1 | C | 08/02/2023 | 65 |
2 | C | 08/02/2023 | 50 |
1 | D | 09/02/2023 | 112 |
2 | D | 09/02/2023 | 105 |
3 | D | 09/02/2023 | 112 |
1 | A | 17/02/2023 | 117 |
2 | A | 17/02/2023 | 138 |
3 | A | 17/02/2023 | 175 |
4 | A | 17/02/2023 | 194 |
1 | B | 15/02/2023 | 191 |
2 | B | 15/02/2023 | 57 |
3 | B | 15/02/2023 | 198 |
1 | C | 17/02/2023 | 166 |
2 | C | 17/02/2023 | 173 |
3 | C | 17/02/2023 | 119 |
1 | D | 16/02/2023 | 135 |
2 | D | 16/02/2023 | 94 |
3 | D | 16/02/2023 | 95 |
1 | E | 14/02/2023 | 167 |
2 | E | 14/02/2023 | 120 |
3 | E | 14/02/2023 | 91 |
4 | E | 14/02/2023 | 60 |
I've used the current formula to try to get it in my calculated column (I need this info as a column):
COUNTX(
FILTER(
ALL(DepartmentSales_DB),
DepartmentSales_DB[Store] = EARLIER(DepartmentSales_DB[Store]) &&
DepartmentSales_DB[Date]<=EARLIER(DepartmentSales_DB[Date])
),1
)
But this formula returns me the next results
Department | Store | Date | Unit Sales | Results |
1 | A | 10/02/2023 | 181 | 4 |
2 | A | 10/02/2023 | 53 | 4 |
3 | A | 10/02/2023 | 54 | 4 |
4 | A | 10/02/2023 | 66 | 4 |
1 | A | 17/02/2023 | 117 | 8 |
2 | A | 17/02/2023 | 138 | 8 |
3 | A | 17/02/2023 | 175 | 8 |
4 | A | 17/02/2023 | 194 | 8 |
Instead of 4, the value should be 1, and instead of 8, 2.
Can someone help me figuring out where is the problem? Thank you.
Hello @JimsonBonilla
The RANK function is useful here.
Try this:
Sequential Rank Calculated Column =
RANK (
DENSE,
ALL ( DepartmentSales_DB[Store], DepartmentSales_DB[Date] ),
ORDERBY ( DepartmentSales_DB[Date] ),
PARTITIONBY ( DepartmentSales_DB[Store] )
)
Regards