Forum Discussion

Ghaston's avatar
Ghaston
Icon for Helper I rankHelper I
8 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
Icon for Helper I rankHelper 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
Icon for Microsoft Employee rankMicrosoft 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
    Icon for Helper I rankHelper I
    7 years ago

    hi v-jiascu-msft

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

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

      Hi Ghaston,

       

      Can you click "Voir les details" and paste the details here?

       

      Best Regards,

      Dale

      • Ghaston's avatar
        Ghaston
        Icon for Helper I rankHelper I
        7 years ago

        hi v-jiascu-msft

        Here is the details : 

         

        it says : 
        Cannot load the data for this visual : 
        Converting the data type from "varchar" to "datetime" has created an off-limit value. The exception was triggered by the IDaraReader inferace.