Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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])
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |