rolling average by half hour

[Ghaston]

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 

[v-jiascu-msft]

Hi @Ghaston,

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

Best Regards,

Dale

[Ghaston]

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]

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]

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]

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

[Ghaston]

hi @v-jiascu-msft

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

[v-jiascu-msft]

Hi @Ghaston,

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

Best Regards,

Dale

[Ghaston]

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]

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

[Ghaston]

here is the link to my file : 
https://drive.google.com/open?id=1lHOTBAjdz5kurSLWpA5RoEydrD6NbKw5
But i'm nit sure if it willwork for your, because, i said before i'm using Direct Query. So the data are not imported into Power BI Desktop

[v-jiascu-msft]

Hi @Ghaston,

That can't work. What's the type of column [Date_Heure]? Seems it isn't a DateTime type. Please check out. The error message is clear. We should check the data type and its values which should be a normal time. For example, 25: 30: 00 isn't a time. 

Best Regards,

Dale

[Ghaston]

it's a date/time type

[Ghaston]

Well, i was trying to recreate the Dashbord in Import mode, so i can share it with you. And then, your code worked perfectly on the new Dashboard (import mode). 
I don't know, why it didn't work on my Direct Query model, even though i'm using the same source file, and the data has the same type...

[v-jiascu-msft]

Hi @Ghaston,

Did you make it work? Do you use the latest version of Power BI Desktop?

Best Regards,
Dale

[v-jiascu-msft]

Hi @Ghaston,

The formula worked well in my test. Please refer to the snapshot below. Do you have any other details you can share?

Best Regards,

Dale

[patrick18]

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