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

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

    Hi Ghaston,

     

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

     

    Best Regards,

    Dale

    • Ghaston's avatar
      Ghaston
      Helper 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. 

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

        Hi Ghaston,

         

        Can you share your file? You can upload it to the cloud drive like OneDirve, GoogleDrive and then paste the download link here.

         

        Best Regards,

        Dale