Add Average of column into a dateaa function

[gaganbansal97]

I am showing a calculated column in a tbale format. I want take the average of that column and add it to the number of interval in DateAdd function.

Below is the ss. The average value of the DateDiff - PC is 71. I want to add this value into arrival date and calculate new date. 

I am using the below formula but not the getting the correct answer

AverageMeasure =

DATEADD(MCSdrop1[ArrivalDate], AVERAGE(MCSdrop1[DateDiff-PC]),DAY)

[v-zhangti · Accepted solution]

Hi, @gaganbansal97 

You can try the following methods.

AverageMeasure = 
VAR _AverageValue = 
CALCULATE(AVERAGE(MCSdrop1[DateDiff-PC]),ALL('MCSdrop1'))
RETURN
MAX(MCSdrop1[ArrivalDate])+_AverageValue

Is this the result you expect? It is not recommended that you use the Dateadd function in this case.

DATEADD returns a table that contains a column of dates, It cannot be written directly in measure.

• The result table includes only dates that exist in the dates column.

• If the dates in the current context do not form a contiguous interval, the function returns an error.

DATEADD function (DAX) - DAX | Microsoft Learn

Best Regards,

Community Support Team _Charlotte

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

[joaoribeiro]

Hi @gaganbansal97 ,

I believe that the problem with your measure is that you are not expanding the average range to consider all values. Please, try to change it using the following logic:

AverageMeasure =
VAR AverageValue = 
CALCULATE(
AVERAGE(MCSdrop1[DateDiff-PC]),
ALL('Date Column')
)

RETURN
DATEADD(MCSdrop1[ArrivalDate], AverageValue,DAY)

With the CALCULATE+ALL you expand the average to all rows in the selected period of time (or you can choose any other dimension you need).

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍

Thanks!

Best regards,
Joao Ribeiro