DAX to average 2 columns and todays date

[Smudgers9]

I have a dataset which has a Created Date Column and a Completed Date column.  I need to work out the average date between the 2 but if the completion date is blank then to use todays date.  Can someone advise?

[Anonymous · Accepted solution]

Hi  @Smudgers9 ,

Here are the steps you can follow：

1. Create measure.

Measure =
var _blank=
IF(
    MAX('Table'[Completed Date])=BLANK(),TODAY(),MAX('Table'[Completed Date]))
return
DATEDIFF(
    MAX('Table'[Created Date]),_blank,DAY)

Measure 2 =
AVERAGEX(
    ALL('Table'),[Measure])

2. Result:

Best Regards,

Liu Yang

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

[Smudgers9]

Worked a treat, thank you

[123abc]

To calculate the average date between the Created Date and the Completed Date columns, considering today's date if the Completed Date is blank, you can use the following DAX formula in Power BI or Power Pivot:

AverageDate =
VAR TodayDate = TODAY()
VAR CompletionDateAdjusted = IF(ISBLANK('YourTable'[Completed Date]), TodayDate, 'YourTable'[Completed Date])
RETURN
AVERAGE('YourTable'[Created Date], CompletionDateAdjusted)

Here's a breakdown of how the formula works:

1. VAR TodayDate = TODAY(): This creates a variable TodayDate and assigns it the value of today's date.

2. VAR CompletionDateAdjusted = IF(ISBLANK('YourTable'[Completed Date]), TodayDate, 'YourTable'[Completed Date]): This creates another variable CompletionDateAdjusted. If the Completed Date is blank, it assigns the value of TodayDate; otherwise, it uses the Completed Date from your dataset.

3. RETURN AVERAGE('YourTable'[Created Date], CompletionDateAdjusted): This calculates the average between the Created Date and the adjusted Completion Date (either the actual Completion Date or today's date if it's blank) and returns the result.

Make sure to replace 'YourTable', 'Created Date', and 'Completed Date' with the actual names of your table and columns in your dataset. You can create a new calculated column or measure using this DAX formula depending on your requirement.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[Smudgers9]

Thanks @123abc  this was a very quick response.

I see what the logic is trying to achieve here which is great, the only issue is my column is not listed within the IF statement, I can only select measures in replacement of my completed date column.

[123abc]

If your Completed Date column is not listed within the IF statement and you can only select measures, it's likely because Power BI doesn't allow direct column references in measures. However, you can create a calculated column in your table to handle the logic and then reference that calculated column in your measure.

Here's how you can do it:

1. Create a Calculated Column:

   Go to your table in Power BI and create a new calculated column with the following formula:

CompletedDateAdjusted = IF(ISBLANK(YourTable[Completed Date]), TODAY(), YourTable[Completed Date])

1. This formula checks if the Completed Date is blank. If it is, it uses today's date; otherwise, it uses the Completed Date from your table.

2. Create a Measure:

   Now, you can create a measure using the calculated column you just created. Use the AVERAGEX function to calculate the average date between the Created Date and the adjusted Completed Date:

AverageDate = AVERAGEX(YourTable, ([Created Date] + YourTable[CompletedDateAdjusted]) / 2)

1. This measure iterates through each row in YourTable, calculates the average date between the Created Date and the adjusted Completed Date (taking today's date if Completed Date is blank), and then computes the average across all rows.

Once you've created the calculated column and the measure, you should be able to use the AverageDate measure in your reports or visualizations to display the average date between the Created Date and the Completed Date, with today's date considered if the Completed Date is blank.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.