How to calculate average for the calendar year

0
votes

My input file consists of column Actual Exp and Actual Min.

Now I want to create a measure for calculating the average for the current calendar year having the latest month of data for Actual Exp and Actual Min.

I want to calculate the average from Jan'21 to Mar'21, and later if data gets added for Apr or May I would like to calculate the average from Jan'21 to May'21 or Apr'21.

Similarly, I want to have from Jan '21 to Dec'21 in Dec'21 and an average from Jan'22 to Feb'22 in Feb'22. I also have a date filter I don't want my date filter to affect the average.

I tried using TOTALYTD and MAX(Date), but it's not working.

Thanks.

1
datematrixpowerbidaxpowerbi-desktop

1 Answers

1
votes

I've not fully understood your question, but hopefully this helps:

AVERAGEX ( ALL(TABLENAME), TABLENAME[COLUMNNAME])

This should give you an average of COLUMNNAME no matter what filters/slicing you have in place. If you wanted to futher restrict this, you can try creating a 2nd measure such as

CALCULATE(AVERAGEX ( ALL(TABLENAME), TABLENAME[COLUMNNAME]), datetable[monthcolumn] IN {"Jan", "Feb", "Mar"})