Forum Discussion

yaya1974's avatar
yaya1974
Icon for Helper III rankHelper III
2 years ago
Solved

8 month average new column

Hello,  I am trying to calculate my 8 month average (Jan-Aug) to populate for remainder of year (Sep-Dec) Can anyone help? Month Actuals Average Total Jan 148   1440 Feb 222   144...
Need Help
  • Irwan's avatar
    Irwan
    1 year ago

    hello yaya1974 

     

    i dont know how your year value in your data, but you can simply add year value as filter.

    Average =
    var _Num = MONTH(CONVERT("2024-"&'Table'[Month]&"-1",DATETIME))
    Return
    IF(
        _Num>8&&VALUE('Table'[Column1])=YEAR(TODAY()),
        AVERAGEX(FILTER(ALL('Table'),VALUE('Table'[Column1])=YEAR(TODAY())),'Table'[Actuals]),
        BLANK()
    )

    as you can see, add in if statement and you can do average on all data before September in on-going year.

     

    But if you want to calculate each year, you can add filter in AVERAGEX

    Average = 
    var _Num = MONTH(CONVERT("2024-"&'Table'[Month]&"-1",DATETIME))
    Return
    IF(
        _Num>8,
        AVERAGEX(FILTER(ALL('Table'),VALUE('Table'[Column1])=YEAR(TODAY())),'Table'[Actuals]),
        BLANK()
    )

     

    Hope this will help.

    Thank you.

Irwan's avatar
Irwan
Icon for Super User rankSuper User
2 years ago

hello yaya1974 

 

is this what you are looking for?

 

create a calculated column with following DAX.

Average =
var _Num = MONTH(CONVERT("2024-"&'Table'[Month]&"-1",DATETIME))
Return
IF(
    _Num>8,
    AVERAGEX(ALL('Table'),'Table'[Actuals]),
    BLANK()
)
 
Hope this will help.
Thank you.
  • yaya1974's avatar
    yaya1974
    Icon for Helper III rankHelper III
    2 years ago

    Hi.  Thank you.   That works, however, I have multiple years, so having a formula looking at just a specific year does not work.  I need a formula  that will filter on the year and the customer and the model.

    Appreciate your help!

    • Irwan's avatar
      Irwan
      Icon for Super User rankSuper User
      1 year ago

      hello yaya1974 

       

      i dont know how your year value in your data, but you can simply add year value as filter.

      Average =
      var _Num = MONTH(CONVERT("2024-"&'Table'[Month]&"-1",DATETIME))
      Return
      IF(
          _Num>8&&VALUE('Table'[Column1])=YEAR(TODAY()),
          AVERAGEX(FILTER(ALL('Table'),VALUE('Table'[Column1])=YEAR(TODAY())),'Table'[Actuals]),
          BLANK()
      )

      as you can see, add in if statement and you can do average on all data before September in on-going year.

       

      But if you want to calculate each year, you can add filter in AVERAGEX

      Average = 
      var _Num = MONTH(CONVERT("2024-"&'Table'[Month]&"-1",DATETIME))
      Return
      IF(
          _Num>8,
          AVERAGEX(FILTER(ALL('Table'),VALUE('Table'[Column1])=YEAR(TODAY())),'Table'[Actuals]),
          BLANK()
      )

       

      Hope this will help.

      Thank you.

      • yaya1974's avatar
        yaya1974
        Icon for Helper III rankHelper III
        1 year ago

        I got it!!! YAYYYYY   finally 🙂   Thank you ğŸ˜€

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      1 year ago

      Hi,

      Share data for multiple years and show the expected result.  Share data in a format that can be pasted in an MS Excel file.