Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

How To Create Measures Over Hours

Hello,   I have read through many of the other messages, but I can't seem to get this one to work. I have sensor data (Please, find it posted) and want to create two measures over 24 hours: 1-) T...
Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi Anonymous ,

    According to your description, here's my solution. 

    Create two measures.

    Average =
AVERAGEX (
    FILTER (
        ALL ( 'Table' ),
        DATEVALUE ( 'Table'[Date /Time] ) = DATEVALUE ( MAX ( 'Table'[Date /Time] ) )
            && 'Table'[Date /Time] <= MAX ( 'Table'[Date /Time] )
    ),
    'Table'[Sensor Values]
)

    Count = COUNTX ( FILTER ( ALL ( 'Table' ), [Average] > 4 ), 'Table'[Date /Time] )

    In my sample, I modify the value of 1/1/21 23:00 to 100, and the count get 1.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

amitchandak's avatar
amitchandak
Super User
4 years ago

Anonymous , Try

Avg Measure =calculate(Average(Table[Sensor Values]), filter(table, table[datetime] <=now() && table[datetime] >=now() -time(24,0,0)))

 

Count GT 4 = calculate(count(Table[Sensor Values]), filter(table, table[datetime] <=now() && table[datetime] >=now() -time(24,0,0) &&  Table[Sensor Values] >4 ))

Anonymous's avatar
Anonymous
Not applicable
4 years ago

amitchandak 

Thank you for your reply 🙂

I tried these measures, but the date/time disappears when I drag the Avg measure to the table visualisation(see photos attached), any idea?Before adding AVG measureafter adding AVG measure