Help

[thanhnt2]

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

[ALLUREAN · Accepted solution]

Hi, @thanhnt2

I tried using measures to achieve what you want.

https://www.dropbox.com/s/ab6vpfzxjk2819l/Average_YTD%28allure-analytics.com%29.pbix?dl=0

[ALLUREAN]

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]

Hi Allurenan

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

your dax, sum all value in  column.

[amitchandak]

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