topic Re: Average for the month in DAX Commands and Tips

Average for the month

Mikeincairns — Sun, 06 Nov 2022 12:28:01 GMT

Hi. I need a monthly average of the following fornightly data. Note: Some months have two fortnights and some have three.

I will be filtering by month and year.

Happy to use M Query or DAX, whichever is easiest.

Re: Average for the month

Anonymous — Sun, 06 Nov 2022 12:39:57 GMT

Hello Mikeincairns ,

Add a new column month

Month = MONTH([Date])

Then the average of each month is

New measure = CALCULATE(AVERAGE([Actual Payroll FTEs per fnight]),ALLEXCEPT(table,[Month]))

Re: Average for the month

Jihwan_Kim — Sun, 06 Nov 2022 12:40:51 GMT

Hi,

Please check the below picture and the attached pbix file.

I tried to create a sample pbix file like below.

It is for creating a table visualization.

In case calendar table does not exist, please try creating a additional columns in the same table like below, and then use Month Year CC column as an axis.

Re: Average for the month

Mikeincairns — Sun, 06 Nov 2022 12:50:04 GMT

Thanks Jihwan_Kim and Anonymous

I also got to it with the CALCULATE function. I had
=CALCULATE(AVGERAGE(tblPayroll[Actual payroll FTEs per fnight]),
FILTER(tblPayroll,tblPayroll[Date] )
)

which seems to work.
I will also try to figure the logic suggested by Anonymous