column for different values depending on the days of the week

[TornDigorn]

Hello, 

I need to generate a "NEW CAGE" column - which will return the value 1 if the value in the "days between" column is 1, and in the case of (only Mondays) the difference in the "days between" column will be equal 1 and 2 (it is very important to generate this 1 in one column

currently 1 is returned in the "NEW CAGE" column if the "days between" column is 1 - I used following command NEW CAGE1 = IF(Table1[Days Between] = 1, "1")

The day name column contains the names of the days of the week that I generated using the command DayName = Format('Table1'[report date],"dddd")

 

poniedzialek = monday

[selimovd · Accepted solution]

Hey @TornDigorn ,

I'm not completely sure if I understood the Monday case correct.

I understood that on Monday the "days between" can be 1 or 2, on the other days it has to be a 1. Is that correct?

In this case the following formula schould work:

NEW CAGE =
SWITCH (
    TRUE (),
    Table1[Days Between] = 1, 1,
    WEEKDAY ( Table1[report date] ) = 2 -- this means Monday
        && (
        Table1[Days Between] = 1 || Table1[Days Between] = 2
        ), 1
)

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic

[v-zhangti · Accepted solution]

Hi, @TornDigorn 

You can try the following methods.

Old Cage = SWITCH(TRUE(),
[Days between]=1,1,
[DayName]="Monday"&&[Days between]>=2,1)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TornDigorn]

hello @selimovd 

That's thats exactly what i was looking for. Thank you ! 🙂 

Could I ask you for help in creating one more formula for the same sheet ?

I need a column named "Old Cage" to return 1 for :

- "days between" greater than 1

- unless it is Monday then "days between" greater than 2

As before, 1's should be displayed in one column

Thanks so much in advance !

[selimovd · Accepted solution]

Hey @TornDigorn ,

sure, that's just an adaption:

Old Cage =
SWITCH (
    TRUE (),
    Table1[Days Between] > 1, 1,
    WEEKDAY ( Table1[report date] ) = 2 -- this means Monday
        && Table1[Days Between] > 2, 1
)

Please don't forget to mark my post as solution if it provided the help to solve your issue. Like this the next person who will stumble across this post will easier find a solution.

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic

 

[v-zhangti · Accepted solution]

Hi, @TornDigorn 

You can try the following methods.

Old Cage = SWITCH(TRUE(),
[Days between]=1,1,
[DayName]="Monday"&&[Days between]>=2,1)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[selimovd · Accepted solution]

Hey @TornDigorn ,

I'm not completely sure if I understood the Monday case correct.

I understood that on Monday the "days between" can be 1 or 2, on the other days it has to be a 1. Is that correct?

In this case the following formula schould work:

NEW CAGE =
SWITCH (
    TRUE (),
    Table1[Days Between] = 1, 1,
    WEEKDAY ( Table1[report date] ) = 2 -- this means Monday
        && (
        Table1[Days Between] = 1 || Table1[Days Between] = 2
        ), 1
)

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic