Sequential Count rows with same date

[Anonymous]

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.

[OwenAuger]

Hello @Anonymous 

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