Week Calculaltion

[RENJITH_R_S]

Friends

 Please help on this to make a calculated column

[PhilipTreacy · Accepted solution]

Hi @RENJITH_R_S 

Then you can change the code to this

Column = SWITCH(
    
        TRUE(),
        
        'Table'[Value] = "d", 1,  //Green
        
        'Table'[Value] = "a", 2,  //Grey

        DATEDIFF(DATEVALUE('Table'[Datevalue]), NOW(), DAY) <= 7, 3, //Blue

        DATEDIFF(DATEVALUE('Table'[Datevalue]), NOW(), DAY) >= 7 && DATEDIFF(DATEVALUE('Table'[Datevalue]), NOW(), DAY) <= 14, 4, //Yellow

        5 //Red

    )

Regards

Phil

[RENJITH_R_S]

@PhilipTreacy thank  you

[amitchandak]

@RENJITH_R_S , Create a new column like

Create a column like
Week Color = Switch( True(),
(Table[Value]) = "d", "Green",
(Table[Value]) = "2", "Grey",
[DateValue] >=today()+-1*WEEKDAY(today(),2)+1 && [DateValue] >= today()+ 7-1*WEEKDAY(today(),2) ,"blue" ,
[DateValue] >= today()+-1*WEEKDAY(today(),2)+1 -7 && [DateValue] >= today()+ 7-1*WEEKDAY(today(),2)-7 ,"yellow" ,
"red"
)

// for monday week WEEKDAY(today(),2)

//for Sunday Week WEEKDAY(today(),1)

[RENJITH_R_S]

Thanks @amitchandak for the response

[amitchandak]

@RENJITH_R_S , On correction. One I mentioned above is column.

For color measure use

Create a measure like
Week Color = Switch( True(),
max(Table[Value]) = "d", "Green",
max(Table[Value]) = "2", "grey",
max([DateValue]) >=today()+-1*WEEKDAY(today(),2)+1 && max([DateValue]) >= today()+ 7-1*WEEKDAY(today(),2) ,"blue" ,
max([DateValue]) >= today()+-1*WEEKDAY(today(),2)+1 -7 && max([DateValue]) >= today()+ 7-1*WEEKDAY(today(),2)-7 ,"yellow" ,
"red"
)

you can use 6 digit hexcode with #

example green = #00FF00

[PhilipTreacy]

Hi @RENJITH_R_S 

It's not entirely clear what you want, for example, what do you mean by this week?  Are you talking within the last 7 days, or the 7 days starting from last Sunday/Monday?

Also, not sure what the colour/number table is for.  Do you want the name of the colour as output or a number?

Try this

Column = SWITCH(
    
        TRUE(),
        
        'Table'[Value] = "d", "Green",
        
        'Table'[Value] = "a", "Grey",

        DATEDIFF(DATEVALUE('Table'[Datevalue]), NOW(), DAY) <= 7, "Blue",

        DATEDIFF(DATEVALUE('Table'[Datevalue]), NOW(), DAY) >= 7 && DATEDIFF(DATEVALUE('Table'[Datevalue]), NOW(), DAY) <= 14, "Yellow",

        DATEDIFF(DATEVALUE('Table'[Datevalue]), NOW(), DAY) >= 14, "Red",

        ""
    )

regards

Phil

[RENJITH_R_S]

Thanks for the response @PhilipTreacy 

my output for the column is numbers 1,2,3,4,5

I wil give conditional formatting based on numbers. 

and all these week calculations are based on Today

[AllisonKennedy]

@RENJITH_R_S  If you want a new column, you can use this formula:

Colour column =

VAR _DVweek = WEEKNUM(table[DateValue])

VAR _Todayweek = WEEKNUM(Today())

RETURN

SWITCH(TRUE()

, table[Value] = "d" , "Green"

, table[Value] = "a", "Grey"

, _DVweek = _Todayweek , "Blue"

, _DVweek +1 = _Todayweek, "Yellow"

,  "Red"

)

Note: This uses week number, so sun-mon. If you want rolling week, you can ammend the formula slightly and use DATEDIFF function.

[RENJITH_R_S]

Thanks for the response @AllisonKennedy