cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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

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.

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.

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors