topic Re: DAX queries for time calculations in DAX Commands and Tips

DAX queries for time calculations

Manaswini94 — Fri, 20 Jan 2023 20:02:23 GMT

Hi, I need assistance with a problem I'm currently facing . Below is the table I'm trying to compare. I have two time columns time-1 and time-2 and wanted to create a table which shows the

count of No that were after time-1 ( i tried this by creating a calculated column time2>time 1 but the results are getting displayed in boolean but need a value instead of boolean) ,count of No that were 1hour before time-1 and count of No that were less than 1hr before time-1.

I have derived the time from time-1 and time-2 so please help me with either datetime column or just time column.

No	time-1	time-2
1	1/12/2023 10:30PM	1/12/2023 10:15PM	before time-1 but less than one hour before time-1
2	1/3/2023 10:00AM	1/3/2023 2:15PM	after time -1
3	1/2/2023 11:30PM	1/2/2023 12:05PM	after time-1
4	1/5/2023 6:00AM	1/5/2023 3:00AM	before time-1
5	1/8/2023 5:15PM	1/10/2023 6:15PM	after time-1
6	1/1/2023 10:00PM	1/1/2023 8:05PM	2 hours before time-1
7	1/5/2023 10:00AM	1/5/2023 9:00AM	before time-1
8	1/3/2023 2:00PM	1/3/2023 1:15PM	before time-1 but less than one hour before time-1
9	1/3/2023 3:00AM	1/2/2023 5:05PM	before time-1
10	1/5/2023 7:50AM	1/5/2023 7:40AM	before time-1 but less than one hour before time-1

Re: DAX queries for time calculations

FreemanZ — Fri, 20 Jan 2023 22:20:45 GMT

hi Manaswini94

try to create three measures like:

After =

VAR _table =

ADDCOLUMNS(

TableName,

"Diff",

CALCULATE(DATEDIFF(TableName[time-1], TableName[time-2], MINUTE))

)

RETURN

COUNTROWS(

FILTER(_table, [Diff]<0)

)

Ealier1 =

VAR _table =

ADDCOLUMNS(

TableName,

"Diff",

CALCULATE(DATEDIFF(TableName[time-1], TableName[time-2], MINUTE))

)

RETURN

COUNTROWS(

FILTER(_table, [Diff]>=0&&[Diff]<60)

)

Ealier2 =

VAR _table =

ADDCOLUMNS(

TableName,

"Diff",

CALCULATE(DATEDIFF(TableName[time-1], TableName[time-2], MINUTE))

)

RETURN

COUNTROWS(

FILTER(_table, [Diff]>=60)

)

Re: DAX queries for time calculations

FreemanZ — Sun, 22 Jan 2023 15:35:51 GMT

hi Manaswini94

try to write three measures like:

after time-1 = VAR _table = ADDCOLUMNS( TableName, "Diff", DATEDIFF(TableName[time-1], TableName[time-2], MINUTE) ) RETURN COUNTROWS( FILTER(_table, [Diff]>=60) ) less than 1hr before time-1 = VAR _table = ADDCOLUMNS( TableName, "Diff", DATEDIFF(TableName[time-1], TableName[time-2], MINUTE) ) RETURN COUNTROWS( FILTER(_table, [Diff]<=0&&[Diff]>-60) ) more than 1hour before time-1 = VAR _table = ADDCOLUMNS( TableName, "Diff", DATEDIFF(TableName[time-1], TableName[time-2], MINUTE) ) RETURN COUNTROWS( FILTER(_table, [Diff]<=-60))

verified and it worked like: