Forum Discussion

thanhnt2's avatar
thanhnt2
New Member
4 years ago
Solved

Help

Hi all, i want to caculate cumulative average of column "Tổng nhân sự". I used dax: 

CALCULATE(SUM('NSLĐ'[Tổng nhân sự]),DATESYTD('NSLĐ'[Date]))
But results returned is current value of column "Tổng nhân sự". I dont know what's wrong? plz help me
Need Help

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    thanhnt2 , Based on level you want to do avg of sum, you need try a measure like this . Use date tbale with TI

     

    example

     


    CALCULATE(averageX(Values('Date'[Date]), calculate( SUM('NSLĐ'[Tổng nhân sự]))),DATESYTD('Date'[Date]))

     

    Or Cumm

    CALCULATE(averageX(Values('Date'[Date]), calculate( SUM('NSLĐ'[Tổng nhân sự]))) ,filter(allselected('Date'),'Date'[date] <=max('Date'[date])))

     

    or

     

    CALCULATE(average('NSLĐ'[Tổng nhân sự]),filter(allselected('Date'),'Date'[date] <=max('Date'[date])))

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

  • ALLUREAN's avatar
    ALLUREAN
    Solution Sage
    4 years ago

    Hi, thanhnt2 

     

    CALCULATE(AVERAGE('NSLĐ'[Tổng nhân sự]),

                    ALL('NSLĐ'),'NSLĐ'[Date]<=EARLIER('NSLĐ'[Date]))

     

    Did I answer your question? Please Like and Mark my post as a solution if it solves your issue. Thanks.

    Appreciate your Kudos !!!

    https://allure-analytics.com/

    • thanhnt2's avatar
      thanhnt2
      New Member
      4 years ago

      Hi Allurenan

      I try use your dax, but value need to break when pass the year. 

      your dax, sum all value in  column.