Custom/Conditional Column IF Date = Today then YES

[NB3]

Hello everyone,

I'm using a dataset that updates everyday and we are trying to add a column to show the following:

(1) IF Date = Today then YES

It would update everyday for the current day.

Also, we need another column to show:

IF Submitted Date = Last Activity Date then YES

BUT

= Yes, if Last Activity is the same day as Submitted

= Yes, if the Last Activity is the following business day before 9h AM.

Thanks in advance for your help!

Nick

[amitchandak · Accepted solution]

@NB3 , Try new columns like

new col 1 =
Switch( true(),
[Date]=today() , "Yes", "No")

new col 2 =
Switch( true(),
[Submitted Date].date = [Last Activity].Date , "Yes",
[Submitted Date].date >= [Last Activity].Date && [Submitted Date].date <= [Last Activity].Date + 1 + time(9,0,0),"Yes",
"No"
)

[amitchandak · Accepted solution]

@NB3 , Try new columns like

new col 1 =
Switch( true(),
[Date]=today() , "Yes", "No")

new col 2 =
Switch( true(),
[Submitted Date].date = [Last Activity].Date , "Yes",
[Submitted Date].date >= [Last Activity].Date && [Submitted Date].date <= [Last Activity].Date + 1 + time(9,0,0),"Yes",
"No"
)

[NB3]

Hi @amitchandak ,

Your second formula works perfectly. I got one question though for the first one. 

It's working but it shows "YES" only for the row whose Date/Time is today at 12:00:00 so I have 1 showing and should have 70.

Also, would you happen to have a formula that shows a "YES" column for applications submitted after 5pm yesterday and before 9am today?

Thanks a lot for your help !

Nick

[amitchandak · Accepted solution]

@NB3 , let us change it a bit 

new col 2 =
Switch( true(),
[Submitted Date].date = [Last Activity].Date , "Yes",
[Submitted Date] >= [Last Activity].Date + time(17,0,0)&& [Submitted Date] <= [Last Activity].Date + 1 + time(9,0,0),"Yes",
"No"
)

[NB3]

Hi @amitchandak 

It works perfectly!

Thanks a lot for your help,

Nick

[lbendlin]

Please define "Today"  - which time zone?

https://radacad.com/solving-dax-time-zone-issue-in-power-bi

For the second one you use this pseudo code:

if Last Activity - Dayvalue(Submitted) < 1.375