Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Manaswini94
Frequent Visitor

DAX queries for time calculations

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.

Notime-1time-2 
11/12/2023 10:30PM1/12/2023 10:15PMbefore time-1 but less than one hour before time-1
21/3/2023 10:00AM1/3/2023 2:15PMafter time -1
31/2/2023 11:30PM1/2/2023 12:05PMafter time-1
41/5/2023 6:00AM1/5/2023 3:00AMbefore time-1
51/8/2023 5:15PM1/10/2023 6:15PMafter time-1
61/1/2023 10:00PM1/1/2023 8:05PM2 hours before time-1
71/5/2023 10:00AM1/5/2023 9:00AMbefore time-1
81/3/2023 2:00PM1/3/2023 1:15PMbefore time-1 but less than one hour before time-1
91/3/2023 3:00AM1/2/2023 5:05PMbefore time-1
101/5/2023 7:50AM1/5/2023 7:40AMbefore time-1 but less than one hour before time-1
1 ACCEPTED SOLUTION
FreemanZ
Super User
Super User

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_0-1674401737675.png

View solution in original post

2 REPLIES 2
FreemanZ
Super User
Super User

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_0-1674401737675.png

FreemanZ
Super User
Super User

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)
)

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.