Forum Discussion
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!
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 )
6 Replies
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 )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]).
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.
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?
