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 want to average for each line ( ligne 11, ligne 12, ligne 13, ligne 14 and ligne 16 in "Ligne" Column. Knowing that i'm using Direct Query, how can i run the average of "Arche-Total_Entres" column every half hour ?? please help

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

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. Best Regards, Dale

Hi Ghaston, Have you created the column? If you refresh the report, all the columns will be refreshed automatically. Best Regards, Dale

Hi v-jiascu-msfti 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.

hi v-jiascu-msftthanks 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 timeHope you can help me out with this.

rolling average by half hour | Microsoft Fabric Community

16 Replies

v-jiascu-msft
Microsoft Employee
7 years ago
Hi Ghaston,

Have you created the column? If you refresh the report, all the columns will be refreshed automatically.

Best Regards,

Dale
- 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
    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

Forum Discussion

rolling average by half hour

16 Replies

Related content

Rolling 12M Average measure

Rolling accumulation

3 Day Rolling Average - DAX

Rolling accumulation

Conditional Rolling Average