Forum Discussion

Ghaston's avatar
Ghaston
Helper I
7 years ago

rolling average by half hour

hi  i'm using power query to connect with a SQL base that refeshes every 5 min. I have a column named "Date_Heure" in the " Date/time " form.  I have a column named "Arche-Total_Entres" that i wan...
Need Help
Ghaston's avatar
Ghaston
Helper I
7 years ago

Hi v-jiascu-msft
i have no problem with refreshing my Dashboard. 
I'm using Direct Query, so my model refeshes automatically, and for every 5mn, a new line is added to my data.  
I juste want to calculate the average of ""Arche-Total_Entres" all the 30 minutes. 


v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
7 years ago

Hi Ghaston,

 

You can create a measure. What is exactly the "all the 30 minutes" and "every 30 minutes"? Is it the latest 30 minutes? Maybe you can try this formula that calculates the average of the latest 30 minutes.

Measure =
VAR maxTime =
    CALCULATE ( MAX ( 'Table1'[Date_Heure] ), ALL ( 'Table1' ) )
RETURN
    CALCULATE (
        AVERAGE ( Table1[Arche-Total_Entres] ),
        FILTER (
            'Table1',
            'Table1'[Date_Heure] <= maxTime
                && 'Table1'[Date_Heure]
                    >= maxTime - TIME ( 0, 30, 0 )
        )
    )

 

Best Regards,

Dale

  • Ghaston's avatar
    Ghaston
    Helper I
    7 years ago

    hi v-jiascu-msft

    thanks for helping me. But i'm not trying to calculate the average for the last 30 mn.
    So here is what i mean by "every 30 mn" : 
    here is my data : 

    as u can see, every 5min, i get a new ligne in the table. But i need to make a graph with the average per 30mn. 
    So in the exemple bellow, i need to make the average for the data with the red line (30mn), and another average for the date with blue line (30mn), and so on.

    Without average, i end up with a graph with so much irrugularity as the one on bellow. I made an average per hour.

    Now i want to make the average per 30mn.
    Hope i was clear this time, and sorry for waisting your time
    Hope you can help me out with this. 

    • v-jiascu-msft's avatar
      v-jiascu-msft
      Microsoft Employee
      7 years ago

      Hi Ghaston,

       

      I'm afraid we can't do it like that. Because we can't get the latest time due to the limits of Direct Query. How about every half an hour based on actual time? 

      1. Create a calculated column.

      Column =
IF (
    TIMEVALUE ( 'exampledata'[Date_Heure] ) > TIME ( HOUR ( [Date_Heure] ), 30, 0 ),
    DATEVALUE ( [Date_Heure] ) + TIME ( HOUR ( [Date_Heure] ), 30, 0 ),
    DATEVALUE ( [Date_Heure] ) + TIME ( HOUR ( [Date_Heure] ), 0, 0 )
)

      2. Put the column in the X-axis.

      rolling_average_by_half_hour

       

      Best Regards,

      Dale

      • Ghaston's avatar
        Ghaston
        Helper I
        7 years ago

        hi v-jiascu-msft

        Seems like a good idea. 
        But infortunately, i get an error when i create the column. 

    • patrick18's avatar
      patrick18
      New Member
      7 years ago

      Hey, did you refresh a dashboard before? there can be problem with columns creating.
      had same problem while was working on my site - speed reading exercises