cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Microsoft Employee

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

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!

1 ACCEPTED SOLUTION
Super User

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 )
``````

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.

6 REPLIES 6
Microsoft Employee

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]).

Super User

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.

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.

Microsoft Employee

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?

Super User

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.

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.

Microsoft Employee

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

Super User

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 )
``````

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.