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-) ...
Need help with DAX
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi Anonymous 
    Here is your file updated based on 34hr averages only at end of each day https://www.dropbox.com/t/XvUdc0zpksXlL9t8
    Your report looks like this

    Once you start having values that generate averages above 4 then "Blank" card will start showing numbers.
    Please let me know if you are now fully satisfied. Thank you.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hello tamerj1 ,

Thank you very much! The first measure is fine.However, and before marking it as an acceptable solution, the second measure needs to count only specific rows; those values that match 24 moving averages. Please see the photo for illustration. Thank you very much and looking forward to your reply!

 

 

 

tamerj1's avatar
tamerj1
Community Champion
4 years ago

Hi Anonymous 
Refer to updated file https://www.dropbox.com/t/6BJyRDp0745ewhPL
You need to create a new date (date only) column:

 

Date = DATE ( YEAR ( Data[Date/Time] ), MONTH ( Data[Date/Time] ), DAY ( Data[Date/Time] ) )

 

The report looks like this

Please let me know if this what you need.

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    There was an error in the "Above 34" measure. I edited and corrected in the previous comments. Download link remains the same.

    • tamerj1's avatar
      tamerj1
      Community Champion
      4 years ago

      Hi Anonymous 
      Here is your file updated based on 34hr averages only at end of each day https://www.dropbox.com/t/XvUdc0zpksXlL9t8
      Your report looks like this

      Once you start having values that generate averages above 4 then "Blank" card will start showing numbers.
      Please let me know if you are now fully satisfied. Thank you.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hello tamerj1 

    Thank you for your effort. I will try to explain it more clearly. Now, we already have the 24 moving averages. that is, we have hours ranging from 00 to 23. The 24 moving average occurs at 23 hour. So, I want to count only those values that are associated with 23 hours and check if they exceeded 4 or not. If they don't exceed 4, then the count returns 0. I hope it makes sense now. Thank you tamerj1 !

    • tamerj1's avatar
      tamerj1
      Community Champion
      4 years ago

      Hello Anonymous ,
      The moving average calculates the the average of the last 24 readings. This happens at the each point starting from the 23rd point onwards as per the table. I think we both agree on this.
      Also lets clear the missing decimal point in the readings. The 34 in my report is 3.4 in yours. Having said that, we should also agree that as per the given sample of data the maximum moving average is 3.5
      The measure "Above 34" counts the number of moving averages that exceed 34 (3.4) within 24 hours (1day)

      • In Jan. 1st we don't have any moving average as the calculatioon starts after 24 hours.
      • In Jan. 2nd we have 24 moving averages 8 of them exceed 3.4
      • In Jan. 3rd we have 24 moving averages 12 of them exceed 3.4
      • After that none of moving averages exceed 3.4 therfore the count is zero (Blank)

      What is missing here please help me clarify it up.