topic Re: Date/TIme variance based on day only (ignoring time) in DAX Commands and Tips

Date/TIme variance based on day only (ignoring time)

Anonymous — Tue, 12 May 2020 18:13:13 GMT

I have got two date/time fields in the table that I am using in a nested if statement that calculates the variance between them. I want power bi to ignore the hours completely and calculate the variance based on days only

Example:

What I get : (01/01/2020 00:00:00) -( 01/01/2020 17:00:00) = 1

What I would like to see (01/01/2020 00:00:00) -( 01/01/2020 17:00:00) = 0 ( because the day is the same)

Formula extract:

VAR = IF(ISBLANK(tasks[TaskFinishDate]), 9999,

IF(tasks[DEP TYPE1]="GIVE",(tasks[ Forecast 9th Mar]- tasks[TaskFinishDate]),

8888))

Thank you

Re: Date/TIme variance based on day only (ignoring time)

Greg_Deckler — Tue, 12 May 2020 19:09:28 GMT

Use INT([datetime])

This will drop the decimal portion of your date time column.

Re: Date/TIme variance based on day only (ignoring time)

Anonymous — Wed, 13 May 2020 08:57:14 GMT

Greg_Deckler Thank you very much, one last question is there a way to apply INT per formula rather than per each date column ( as my formula has quite a few lines) ?

so that instead of INT(date1)-INT(date2)

something like INT([date1]-[date2]) - this didnt work but wondering if there is a way to do this ?

Thank you

Re: Date/TIme variance based on day only (ignoring time)

Anonymous — Wed, 13 May 2020 09:47:20 GMT

The best way is to store date and time separately. Then you won't have such problems.

Best
D

Re: Date/TIme variance based on day only (ignoring time)

Greg_Deckler — Wed, 13 May 2020 12:14:21 GMT

Might be able to use TRUNC