cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DXF_83
Helper III
Helper III

Daily average in calculated column

Hi Community,

I'm trying to find a way to calculate a daily average of a hourly prices of a certain market on historic series of 5 years. I need it in a column once I need to take that figures for a further join table.

The data is in this "Sample Table":

DatePrice
2021-05-065
2021-05-063
2021-05-061
2021-05-061
2021-05-062
2021-05-052
2021-05-053
2021-05-051
2021-05-054
2021-05-055

 

And the desired output is this format:

DatePriceDaily avg
2021-05-0652.4
2021-05-0632.4
2021-05-0612.4
2021-05-0612.4
2021-05-0622.4
2021-05-0523
2021-05-0533
2021-05-0513
2021-05-0543
2021-05-0553

 

I'm trying to use:

Daily Avg = 

calculate(AVERAGE("Sample Table"[Price]),"Sample Table"[Date]), but output is the hourly price for each row, instead of the average.
 
Any clue on this, please?
 
Thanks in advance.
 
Best regards
1 ACCEPTED SOLUTION
FrankAT
Super User II
Super User II

Hi @DXF_83 

take a look at the following solution:

 

06-05-_2021_15-41-18.png

 

Average by Day = 
CALCULATE ( AVERAGE ( 'Table'[Price] ), ALLEXCEPT ( 'Table', 'Table'[Date] ) )

 

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

View solution in original post

3 REPLIES 3
DXF_83
Helper III
Helper III

Thanks @FrankAT and @amitchandak for your tips and help!

Cheers

FrankAT
Super User II
Super User II

Hi @DXF_83 

take a look at the following solution:

 

06-05-_2021_15-41-18.png

 

Average by Day = 
CALCULATE ( AVERAGE ( 'Table'[Price] ), ALLEXCEPT ( 'Table', 'Table'[Date] ) )

 

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

View solution in original post

amitchandak
Super User IV
Super User IV

@DXF_83 , Try a new column like

 

averageX(filter(Table, [Date] =earlier([Date])),[Price])



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors