Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I'm trying to calculate the previous hour and previous day for a simple table in powerbi. For previous hour, I have the following calculation. I would like the count for IDs with status 3 for the previous hour.
PreviousHourTest with status 3 =
CALCULATE (
COUNT ( TestTable[Id] ),
TestTable[Status] = 3,
( DATEDIFF ( TestTable[Created Time and Date], TODAY (), HOUR ) <= 1 )
)
For previous day, I have the following, I would also like to filter this for status = 3:
Count Prev Day =
CALCULATE (
COUNT ( TestTable[Id] ),
FILTER (
ALL ( TestTable[Created Time and Date] ),
'TestTable'[Created Time and Date] = MAX ( TestTable[Created Time and Date] )
)
)
Unfortunately, the results I receive are not what I expect.
My table is as follows:
Can anyone help me please?
Solved! Go to Solution.
@JohnUnfiro Try these:
Previous Hour =
CALCULATE(
COUNT( TestTable[Id]),
ALL(TestTable),
TestTable[Status] = 3,
TestTable[Created Time and Date] >= TODAY()-1/24,
TestTable[Created Time and Date] < TODAY()
)
Previous Day
CALCULATE(
COUNT( TestTable[Id]),
ALL(TestTable),
TestTable[Status] = 3,
TestTable[Created Time and Date] >= DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))-1,
TestTable[Created Time and Date] < DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))
)
@JohnUnfiro Try these:
Previous Hour =
CALCULATE(
COUNT( TestTable[Id]),
ALL(TestTable),
TestTable[Status] = 3,
TestTable[Created Time and Date] >= TODAY()-1/24,
TestTable[Created Time and Date] < TODAY()
)
Previous Day
CALCULATE(
COUNT( TestTable[Id]),
ALL(TestTable),
TestTable[Status] = 3,
TestTable[Created Time and Date] >= DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))-1,
TestTable[Created Time and Date] < DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))
)
Thanks @Greg_Deckler. Previous day looks good.
I added a new line to the data but I get a blank value for previous hour. It's 21:53 here now.
Does previous hour mean within the last 60 minutes or is it the time from 20:00 to 21:00?
I would like the value between 20:00 and 21:00. Thanks again.
@JohnUnfiro As coded it was in the last 60 minutes. Let me think, maybe:
Previous Hour =
VAR __Today = DATE(YEAR(NOW()),MONTH(NOW()),DAY(NOW()))
VAR __CurrentHour = HOUR(__Today)
VAR __1HourAgo = DATE(YEAR(NOW()),MONTH(NOW()),DAY(NOW()))-1/24
VAR __LastHour = HOUR(__1HourAgo)
VAR __Max = __Today + __CurrentHour/24
VAR __Min = __1HourAgo + __LastHour/24
RETURN
CALCULATE(
COUNT( TestTable[Id]),
ALL(TestTable),
TestTable[Status] = 3,
TestTable[Created Time and Date] >= __Min,
TestTable[Created Time and Date] <= __Max
)
VAR __1HourAgo looks off to me (always 11 PM?).
It seems to me like this should work (but I haven't tested it):
Previous Hour =
VAR __Max = NOW ()
VAR __Min = __Max - 1 / 24
RETURN
CALCULATE (
COUNT ( TestTable[Id] ),
ALL ( TestTable ),
TestTable[Status] = 3,
TestTable[Created Time and Date] >= __Min,
TestTable[Created Time and Date] <= __Max
)
@AlexisOlson Yeah, it should have just been:
VAR __1HourAgo = NOW()-1/24
User | Count |
---|---|
100 | |
89 | |
82 | |
76 | |
71 |
User | Count |
---|---|
113 | |
104 | |
100 | |
73 | |
65 |