Forum Discussion

vnanduri's avatar
vnanduri
Frequent Visitor
6 years ago
Solved

Set flags for Fridays

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

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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

  • tex628's avatar
    tex628
    Community Champion
    6 years ago

    Create a column:

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



    / J

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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)