Forum Discussion

TsW08's avatar
TsW08
New Member
2 years ago
Solved

Month plus month average

Hi,

 

I have a table in Power Bi with all our staff, and costings. The tsblr has a "Date" column, as well as "Year", "Month" & "Quarter" and then  a "Cost" column.

 

I have been looking for a way to calculate the average "Cost" in a specific way but unable to find an answer.

 

What I need, is to sum for each month (for monthly cost totals)

Then

Average between month 1 & 2, 

Average between month 2 & 3 

And so on.... 

Then sum all the above average and divide by 12. 

 

  •  Any help is much appreciated 👏

 

  • Anonymous's avatar
    Anonymous
    2 years ago

    Thanks for your prompt reply! bhanu_gautam 

    Hi TsW08 

    Based on your needs, I have created the following table.



    Then you can try the following dax to get "total cost", "average between two months", "average divide by 12".

    Total = 
VAR _month = SELECTEDVALUE('Table'[Month])
VAR _year = SELECTEDVALUE('Table'[Year])

RETURN
CALCULATE(SUM('Table'[Cost]),FILTER(ALL('Table'),'Table'[Year]=_year && 'Table'[Month]=_month))



    Average = 
VAR _month = SELECTEDVALUE('Table'[Month])
VAR _year = SELECTEDVALUE('Table'[Year])
VAR _current = CALCULATE([Total],FILTER(ALL('Table'),'Table'[Year] = _year && 'Table'[Month] = _month))
VAR _previous = CALCULATE([Total],FILTER(ALL('Table'),'Table'[Year] = _year && 'Table'[Month] = _month - 1))

RETURN IF(_previous = BLANK(),[Total],(_current + _previous)/2)

     

    average and divide by 12 = 
var _year = SELECTEDVALUE('Table'[Year])
RETURN
SUMX(FILTER(ALL('Table'),'Table'[Year]=_year),'Table'[Average])/12



    Result:

     

     

     

     

     

    Best Regards,

    Jayleny

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     



3 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    2 years ago

    TsW08 ,  Create a calculated column for monthly totals

     

    MonthlyTotal =
    CALCULATE(
    SUM(StaffCostings[Cost]),
    ALLEXCEPT(StaffCostings, StaffCostings[Year], StaffCostings[Month])
    )

     

    Create a calculated column for the moving average between consecutive months:

    DAX
    MovingAverage =
    VAR CurrentMonth = StaffCostings[Month]
    VAR CurrentYear = StaffCostings[Year]
    VAR PreviousMonth = IF(CurrentMonth = 1, 12, CurrentMonth - 1)
    VAR PreviousYear = IF(CurrentMonth = 1, CurrentYear - 1, CurrentYear)
    VAR PreviousMonthTotal =
    CALCULATE(
    SUM(StaffCostings[MonthlyTotal]),
    StaffCostings[Year] = PreviousYear,
    StaffCostings[Month] = PreviousMonth
    )
    RETURN
    IF(
    ISBLANK(PreviousMonthTotal),
    BLANK(),
    (StaffCostings[MonthlyTotal] + PreviousMonthTotal) / 2
    )

     

    Create a measure to sum the moving averages and divide by 12

    DAX
    FinalAverage =
    VAR MovingAverages =
    CALCULATETABLE(
    VALUES(StaffCostings[MovingAverage]),
    NOT(ISBLANK(StaffCostings[MovingAverage]))
    )
    VAR SumOfMovingAverages = SUMX(MovingAverages, [MovingAverage])
    RETURN
    SumOfMovingAverages / 12

     

     

    • TsW08's avatar
      TsW08
      New Member
      2 years ago

       Hi bhanu_gautam 

      Thank you for the fast response. The formula seems to return an empty column (see images below). Does this look correct? 

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Thanks for your prompt reply! bhanu_gautam 

    Hi TsW08 

    Based on your needs, I have created the following table.



    Then you can try the following dax to get "total cost", "average between two months", "average divide by 12".

    Total = 
VAR _month = SELECTEDVALUE('Table'[Month])
VAR _year = SELECTEDVALUE('Table'[Year])

RETURN
CALCULATE(SUM('Table'[Cost]),FILTER(ALL('Table'),'Table'[Year]=_year && 'Table'[Month]=_month))



    Average = 
VAR _month = SELECTEDVALUE('Table'[Month])
VAR _year = SELECTEDVALUE('Table'[Year])
VAR _current = CALCULATE([Total],FILTER(ALL('Table'),'Table'[Year] = _year && 'Table'[Month] = _month))
VAR _previous = CALCULATE([Total],FILTER(ALL('Table'),'Table'[Year] = _year && 'Table'[Month] = _month - 1))

RETURN IF(_previous = BLANK(),[Total],(_current + _previous)/2)

     

    average and divide by 12 = 
var _year = SELECTEDVALUE('Table'[Year])
RETURN
SUMX(FILTER(ALL('Table'),'Table'[Year]=_year),'Table'[Average])/12



    Result:

     

     

     

     

     

    Best Regards,

    Jayleny

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.