Calculate average of last category index

[Anonymous]

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

[Ashish_Mathur]

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.

[Anonymous]

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?

[Jihwan_Kim]

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.

[Jihwan_Kim · Accepted 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] )
    )