Rank day of the week by date

[reynaldo_malave]

Hi guys,

I am working on a forecast. I am evaluating last 4 weeks and need to rank dates by day of the week like the following table:

Date                          Dow      Rank

02-07-2021	5	1
03-07-2021	6	1
04-07-2021	0	1
05-07-2021	1	1
06-07-2021	2	1
07-07-2021	3	1
08-07-2021	4	1
09-07-2021	5	2
10-07-2021	6	2
11-07-2021	0	2
12-07-2021	1	2
13-07-2021	2	2
14-07-2021	3	2
15-07-2021	4	2
16-07-2021	5	3
17-07-2021	6	3
18-07-2021	0	3
19-07-2021	1	3
20-07-2021	2	3
21-07-2021	3	3
22-07-2021	4	3
23-07-2021	5	4
24-07-2021	6	4
25-07-2021	0	4
26-07-2021	1	4
27-07-2021	2	4
28-07-2021	3	4
29-07-2021	4	4

I develop the following dax:

forecast =
VAR fecha28dias =
    TODAY () - 29
    
VAR ventas28dias =
    CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZE ( Venta, Venta[fecha] ),
            "Venta", [$CLP Total],
            "diasemana", WEEKDAY ( Venta[fecha], 2 ),
            "indice", 
            	var fecha = Venta [Fecha] 
            	var diasemana = WEEKDAY(Venta[Fecha], 2)
            	return
            		CALCULATE(
            			RANK.EQ( fecha, Venta[fecha], ASC ),
            			FILTER( KEEPFILTERS( Venta ),  WEEKDAY(Venta[Fecha], 2) = diasemana )
            		)
            		
        ),
        Calendario[Fecha] >= fecha28dias
    )

This return a table like the following table, which gives me 1 only on the first week, I don´t really undesrtand the beahavior.

I did reference the following article, https://community.powerbi.com/t5/Desktop/DAX-Ranking-Date/m-p/98237 

Any help would be greately appreciated.

Thanks, 

Reynaldo

[lbendlin]

That's not really a ranking question, it's more related to date math.  You can subtract "Today" from the date, and integer divide the difference by 7. Add the desired offset (for example +1) as needed.

[reynaldo_malave]

thanks @lbendlin , 

I did try your solution but is not what I am looking for. The result is increasing while I move further apart form today. I need the biggest number to match my closest sales. 

I did solve it creating the table in my model and the using it within my dax code. I would have love to do it all using variables inside the code.

Here is the code and the result of your "rank" vs my "rank2"

fecharankeada =

    CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZE ( Venta, Venta[fecha] ),            
            "rank", ROUNDUP( DATEDIFF(  Venta[fecha], ultimafecha, day ) / 7, 0 ),
            "rank2", LOOKUPVALUE( Rank28dias[rankfecha], Rank28dias[Fecha], Venta[fecha] )
        ),
        Calendario[Fecha] >= fecha28dias
    )

[reynaldo_malave · Accepted solution]

I did find a solution here https://community.powerbi.com/t5/Desktop/DAX-Ranking-Date/m-p/98237 

Althought I was looking for a table within my code.