Help to calculate average by category and by month

[fabiocrgomes]

Hello Community,
I would like to create a new average column in my table that is exemplified below. This average should be calculated by month and by category.  
 

Date	Category	Value
2022-01-01	A	12000
2022-01-01	B	87000
2022-01-01	C	38000
2022-01-01	D	91000
2022-01-02	A	11000
2022-01-02	B	85000
2022-01-02	C	38000
2022-01-02	D	90000
2022-01-03	A	11500
2022-01-03	B	12000
2022-01-03	C	38500
2022-01-03	D	91000

Thanks in advance for your help

Fabio

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

It is for creating a new column.

avg by month and category CC =
VAR currentmonthend =
    EOMONTH ( Data[Date], 0 )
VAR currentcategory = Data[Category]
VAR result =
    AVERAGEX (
        FILTER (
            Data,
            EOMONTH ( Data[Date], 0 ) = currentmonthend
                && Data[Category] = currentcategory
        ),
        Data[Value]
    )
RETURN
    result

[fabiocrgomes]

Thanks @Jihwan_Kim, it works perfectly