Help with creating column which calulcates averrage values based on month + year value

[vbvbvb]

Hello community, 

Trying to see if there's a n easy way to implement column with DAX calculating following:

There is a table with a item, 50-60 per month, I want to calculate average values for them per month per each item conditionally based on their Month and Year , so as result, column would be populated with Average values per respective dates, taking to consideration Month + Year of items:

ID	Values	Month	Year	Average value ??
12345	10	October	2022	Sum of all values in October 2022 / count of October 2022 = 10 +5 /2 = 7.5
23456	20	November	2022	Sum of all values in Novemeber 2022/ count of November 2022 = 20/1 = 20
34567	15	September	2022	Sum of all values in September 2022/ count of September 2022
45678	5	October	2022	Sum of all values in October 2022 / count of October 2022
567890	10	August	2022	Sum of all values in August 2022/ count of August 2022
678901	20	October	2023	Sum of all values in October 2023/ count of October 2023
789012	10	August	2023	Sum of all values in August 2023/ count of August 2023
890123	5	November	2023	Sum of all values in Novemeber 2023/ count of Novemeber 2023

I suppose this can be achives by combining COUNTROWS + AVERAGE + PARALELLPERIOD, but can't wrap my head around actualy DAX to make it work correctly -- would appreaciate your guidance!

[vbvbvb]

Thanks , what is expected format of  Data[Year]  and Data[Month] ? I get this error when using thse fields:

Currently these values are calculcated as Year= YEAR(Data[Date]).

[Jihwan_Kim]

Hi,

The suggest DAX formula is for creating a new column.

Could you please share your sample pbix file's link here? And then I can try to look into your sample pbix file to come up with a more accurate solution.

Thank you.

[vbvbvb]

As suspected, this was an issue with format of these two columns -- I did new extraction of year + month and your DAX worked, thanks!

If I want to add extra argument to FILTER to also only consider another column for this calculation,  for example if column = "Test" text value, can I add it directly to this formula?

[Jihwan_Kim]

Hi,

Thank you for your reply, and I think it will work. If not, please share your sample pbix file and then I can try to look into it.

Thanks.

[vbvbvb]

It did, i checked and seems that your solution was spot on , thansk once again!

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

AVG value CC =
VAR _newtable =
    FILTER (
        Data,
        Data[Year] = EARLIER ( Data[Year] )
            && Data[Month] = EARLIER ( Data[Month] )
    )
RETURN
    SUMX ( _newtable, Data[Values] ) / COUNTROWS ( _newtable )