DAX queries for time calculations

[Manaswini94]

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

[FreemanZ · Accepted solution]

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:

[FreemanZ]

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)

)