Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
Solved! Go to 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:
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:
hi @Manaswini94
try to create three measures like:
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
7 | |
7 | |
6 | |
6 |
User | Count |
---|---|
27 | |
12 | |
10 | |
9 | |
6 |