Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Mikeincairns
Frequent Visitor

Average for the month

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.

 

Mikeincairns_0-1667737653934.png

 

 

1 ACCEPTED SOLUTION
Mikeincairns
Frequent Visitor

Thanks @Jihwan_Kim and @eliasayy 

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 @eliasayy 

View solution in original post

3 REPLIES 3
Mikeincairns
Frequent Visitor

Thanks @Jihwan_Kim and @eliasayy 

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 @eliasayy 

Jihwan_Kim
Super User
Super User

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.

 

Jihwan_Kim_1-1667738416606.png

 

 

Jihwan_Kim_0-1667738323791.png

 

 

 

 

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.


Go to My LinkedIn Page


eliasayy
Impactful Individual
Impactful Individual

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

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors