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.

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

Hi Engineer Anonymous 
Here is the sample file with solution https://www.dropbox.com/t/Wuw3IVn7ofWHqEr5
Dealing with time in dax is not optimum therefore I recommend that you start with adding an index column to your table. It will cost you one click in power query

The code for the two measures is:

 

 

 

Moving Average 24hrs = 
VAR CurrentIndex =
    MIN (Data[Index] )
VAR ShiftedIndex =
    CurrentIndex - 24
VAR MovingAverage =
    CALCULATE (
        AVERAGE ( Data[Sensor Values] ),
        REMOVEFILTERS ( Data[Date/Time] ),
        Data[Index] >= ShiftedIndex,
        Data[Index] <= CurrentIndex
    )
VAR Result =
    IF ( 
        CurrentIndex >= 24,
        MovingAverage
    )
RETURN
    Result
Above 34 = 
VAR FilterTable =
    FILTER ( 
        VALUES ( Data[Index] ),
        [Moving Average 24hrs] > 34
    )
VAR Result =
    COUNTROWS ( FilterTable )
RETURN
    Result

 

 

 

And this is how thw report looks like

If this answers your query, I kindly request you to mark this reply as acceptable solution. Have agreat day!

  • 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
      Icon for Community Champion rankCommunity 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
        Icon for Community Champion rankCommunity 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.