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 there,
I am trying to find a solution / measure to calculate the Average of only the last "Date Value Index" Values for each date. For example, the average for table below would be (140 + 190 + 123 + 190) / 4.
Has anyone done this before or could please include a link to the solution?
Thanks.
Date | Value | Index | Date Value Index |
01/01/2022 | 100 | 1 | 1 |
01/01/2022 | 120 | 2 | 2 |
01/01/2022 | 140 | 3 | 3 |
02/01/2022 | 190 | 4 | 1 |
03/01/2022 | 122 | 5 | 1 |
03/01/2022 | 123 | 6 | 2 |
04/01/2022 | 190 | 7 | 1 |
Solved! Go to Solution.
Hi,
Please check the below picture and the attached pbix file.
It is for creating a new measure.
Expected measure: =
VAR maxindex_samedate =
ADDCOLUMNS (
SUMMARIZE ( Data, Data[Date] ),
"@maxindex", CALCULATE ( MAX ( Data[Index] ) )
)
RETURN
CALCULATE (
AVERAGE ( Data[Value] ),
TREATAS ( maxindex_samedate, Data[Date], Data[Index] )
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi,
Here are 2 ways of solving the problem
Approach 1 (with a calculated column formula and a measure)
Write this calculated column formula
To consider = Data[Date Value Index]=CALCULATE(MAX(Data[Date Value Index]),FILTER(Data,Data[Date]=EARLIER(Data[Date])))
Write this measure
Measure with spare column = CALCULATE(AVERAGE(Data[Value]),Data[To consider]=TRUE())
Approach 2 (with a measure only)
Measure without spare column = averagex(filter(SUMMARIZE(GENERATE(VALUES(Data[Date]),VALUES(Data[Date Value Index])),Data[Date],Data[Date Value Index],"A",[Date value],"B",[Max of date value index]),[A]=[B]),[Values])
Hope this helps.
Hi @Jihwan_Kim that's exactly what I was after - thank you. Would you recommend having the index as a calculated column or create it within Power Query by merging two identical tables together?
Hi,
Thank you for your feedback.
I prefer having an index column created within Power Query Editor, since Power Query Editor has the function called ADD INDEX COLUMN.
Thank you.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi,
Please check the below picture and the attached pbix file.
It is for creating a new measure.
Expected measure: =
VAR maxindex_samedate =
ADDCOLUMNS (
SUMMARIZE ( Data, Data[Date] ),
"@maxindex", CALCULATE ( MAX ( Data[Index] ) )
)
RETURN
CALCULATE (
AVERAGE ( Data[Value] ),
TREATAS ( maxindex_samedate, Data[Date], Data[Index] )
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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 |
---|---|
112 | |
100 | |
77 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |