Previous hour and previous day, DAX.

[JohnUnfiro]

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? 

[Greg_Deckler · Accepted 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]

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. 

[Greg_Deckler]

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

[AlexisOlson]

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
    )

[Greg_Deckler]

@AlexisOlson Yeah, it should have just been: 

VAR __1HourAgo = NOW()-1/24