Forum Discussion

Mikeincairns's avatar
Mikeincairns
Frequent Visitor
3 years ago
Solved

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.

 

 

 

Need Dax
Need Help DAX measure
  • Mikeincairns's avatar
    Mikeincairns
    3 years ago

    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 

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

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

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

    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.

     

     

     

     

     

     

     

  • Mikeincairns's avatar
    Mikeincairns
    Frequent Visitor
    3 years ago

    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