Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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":
Date | Price |
2021-05-06 | 5 |
2021-05-06 | 3 |
2021-05-06 | 1 |
2021-05-06 | 1 |
2021-05-06 | 2 |
2021-05-05 | 2 |
2021-05-05 | 3 |
2021-05-05 | 1 |
2021-05-05 | 4 |
2021-05-05 | 5 |
And the desired output is this format:
Date | Price | Daily avg |
2021-05-06 | 5 | 2.4 |
2021-05-06 | 3 | 2.4 |
2021-05-06 | 1 | 2.4 |
2021-05-06 | 1 | 2.4 |
2021-05-06 | 2 | 2.4 |
2021-05-05 | 2 | 3 |
2021-05-05 | 3 | 3 |
2021-05-05 | 1 | 3 |
2021-05-05 | 4 | 3 |
2021-05-05 | 5 | 3 |
I'm trying to use:
Daily Avg =
Solved! Go to Solution.
Hi @Anonymous
take a look at the following solution:
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)
Hi @Anonymous
take a look at the following solution:
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)
@Anonymous , Try a new column like
averageX(filter(Table, [Date] =earlier([Date])),[Price])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
118 | |
107 | |
70 | |
70 | |
43 |
User | Count |
---|---|
148 | |
106 | |
104 | |
89 | |
65 |