Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Add Average of column into a dateaa function

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's avatar
    v-zhangti
    3 years ago

    Hi, Anonymous 

     

    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.

2 Replies

  • joaoribeiro's avatar
    joaoribeiro
    Icon for Impactful Individual rankImpactful Individual
    3 years ago

    Hi Anonymous ,

     

    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

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi, Anonymous 

     

    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.