topic Re: Rank day of the week by date in DAX Commands and Tips

Rank day of the week by date

reynaldo_malave — Fri, 30 Jul 2021 19:01:10 GMT

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

Re: Rank day of the week by date

lbendlin — Mon, 02 Aug 2021 21:49:29 GMT

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.

Re: Rank day of the week by date

reynaldo_malave — Tue, 03 Aug 2021 15:02:22 GMT

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 )

Re: Rank day of the week by date

reynaldo_malave — Thu, 05 Aug 2021 14:16:17 GMT

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.