Set flags for Fridays

[vnanduri]

Hi Team,

I have a query as below,

My dataset has only Friday dates, So my question is, the most recent friday should be numbered as zero and the preceding fridays should be numbered -1, -2... and so on.

For example, since todays' date is 02/06/2020, the data should be like 

29-05-2020 - 0

22-05-2020 - (-1)

15-05-2020 - (-2)

and so on...

I'll be having multiple records for each date.

Regards,

Vijay

[v-yiruan-msft · Accepted solution]

Hi @vnanduri ,

You can create a calculated column as below:

Flag = 
VAR Sweek =
    'Date'[Date] - WEEKDAY ( 'Date'[Date], 2 ) + 1
VAR Cweek =
    TODAY () - WEEKDAY ( TODAY (), 2 ) + 1
RETURN
    ( Sweek - Cweek ) / 7 + 1

Best Regards

Rena

[vnanduri]

Hi,

This works for me.

Cheers,

VJ

[amitchandak]

@vnanduri 

Last Friday = today()+-1*if(WEEKDAY(today())-6<0,WEEKDAY(today())+1,WEEKDAY(today())-6)

Last to Last Friday = today()+-1*if(WEEKDAY(today())-6<0,WEEKDAY(today())+1,WEEKDAY(today())-6) -7

keep on getting subtracting -7 to get more

You can actually use date diff from Last Friday  and take mod(reminder) or quotient of that with 7 to get the desired result

quotient (datediff(Date[Date],[Last Friday],day),7)

mod(datediff(Date[Date],[Last Friday],day),7)

[tex628]

Create a column:

Column = (RANKX('Table';'Table'[Date];'Table'[Date];DESC;Dense) -1) * -1

/ J