How To Create Measures Over Hours

[Anonymous]

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-) The first measure is a rolling a verage of sensor values over 24 hours 

2-) The second measure is a count of the sensor values 24 hours rolling average which exceeded 4. That is, I want to count only those sensor values of the newly created measure that exceeded 4. If there is no exceedance, it should return 0. 

Thank you 

Date /Time          Sensor Values 

01.01.2021 00:00	0,38
01.01.2021 01:00	0,30
01.01.2021 02:00	0,30
01.01.2021 03:00	0,31
01.01.2021 04:00	0,31
01.01.2021 05:00	0,32
01.01.2021 06:00	0,32
01.01.2021 07:00	0,31
01.01.2021 08:00	0,31
01.01.2021 09:00	0,31
01.01.2021 10:00	0,30
01.01.2021 11:00	0,31
01.01.2021 12:00	0,31
01.01.2021 13:00	0,32
01.01.2021 14:00	0,33
01.01.2021 15:00	0,33
01.01.2021 16:00	0,34
01.01.2021 17:00	0,33
01.01.2021 18:00	0,33
01.01.2021 19:00	0,34
01.01.2021 20:00	0,35
01.01.2021 21:00	0,35
01.01.2021 22:00	0,34
01.01.2021 23:00	0,34
02.01.2021 00:00	0,34
02.01.2021 01:00	0,33
02.01.2021 02:00	0,33
02.01.2021 03:00	0,32
02.01.2021 04:00	0,32
02.01.2021 05:00	0,32
02.01.2021 06:00	0,33
02.01.2021 07:00	0,34
02.01.2021 08:00	0,34
02.01.2021 09:00	0,34
02.01.2021 10:00	0,35
02.01.2021 11:00	0,35
02.01.2021 12:00	0,35
02.01.2021 13:00	0,35
02.01.2021 14:00	0,35
02.01.2021 15:00	0,37
02.01.2021 16:00	0,37
02.01.2021 17:00	0,37
02.01.2021 18:00	0,37
02.01.2021 19:00	0,35
02.01.2021 20:00	0,36
02.01.2021 21:00	0,36
02.01.2021 22:00	0,36
02.01.2021 23:00	0,35
03.01.2021 00:00	0,34
03.01.2021 01:00	0,35
03.01.2021 02:00	0,35
03.01.2021 03:00	0,35
03.01.2021 04:00	0,33
03.01.2021 05:00	0,32
03.01.2021 06:00	0,33
03.01.2021 07:00	0,33
03.01.2021 08:00	0,31
03.01.2021 09:00	0,30
03.01.2021 10:00	0,29
03.01.2021 11:00	0,30
03.01.2021 12:00	0,29
03.01.2021 13:00	0,29
03.01.2021 14:00	0,29
03.01.2021 15:00	0,30
03.01.2021 16:00	0,29
03.01.2021 17:00	0,30
03.01.2021 18:00	0,29
03.01.2021 19:00	0,29
03.01.2021 20:00	0,29
03.01.2021 21:00	0,29
03.01.2021 22:00	0,28
03.01.2021 23:00	0,28
04.01.2021 00:00	0,28
04.01.2021 01:00	0,27
04.01.2021 02:00	0,26
04.01.2021 03:00	0,27
04.01.2021 04:00	0,27
04.01.2021 05:00	0,29
04.01.2021 06:00	0,30
04.01.2021 07:00	0,31
04.01.2021 08:00	0,32
04.01.2021 09:00	0,32
04.01.2021 10:00	0,32
04.01.2021 11:00	0,33
04.01.2021 12:00	0,32
04.01.2021 13:00	0,33
04.01.2021 14:00	0,43
04.01.2021 15:00	0,34
04.01.2021 16:00	0,34
04.01.2021 17:00	0,32
04.01.2021 18:00	0,31
04.01.2021 19:00	0,31
04.01.2021 20:00	0,28
04.01.2021 21:00	0,29
04.01.2021 22:00	0,29
04.01.2021 23:00	0,29
05.01.2021 00:00	0,51
05.01.2021 01:00	0,28
05.01.2021 02:00	0,29
05.01.2021 03:00	0,29
05.01.2021 04:00	0,30
05.01.2021 05:00	0,30
05.01.2021 06:00	0,31
05.01.2021 07:00	0,31
05.01.2021 08:00	0,32
05.01.2021 09:00	0,32
05.01.2021 10:00	0,31
05.01.2021 11:00	0,31
05.01.2021 12:00	0,31
05.01.2021 13:00	0,30
05.01.2021 14:00	0,30
05.01.2021 15:00	0,33
05.01.2021 16:00	0,32
05.01.2021 17:00	0,33
05.01.2021 18:00	0,33
05.01.2021 19:00	0,33
05.01.2021 20:00	0,32
05.01.2021 21:00	0,32
05.01.2021 22:00	0,32
05.01.2021 23:00	0,30

[tamerj1]

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]

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]

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.

[Anonymous]

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]

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.

[tamerj1]

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

[tamerj1 · Accepted solution]

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.

[TOK]

Hi @Anonymous,

I guess the easiest way is to use the filter pane on the right side. If this is the only information you want to report, I would probably use page filter instead of visual filter.

Liked this post? More Content on:
https://www.youtube.com/channel/UC2lAgCgfyLCHsRv0h-ETBWQ

[Anonymous]

@TOK  Thank you for your suggestion. I need these measures in DAX because I want to use them in paginated reports!