Create an "AverageDay" measure

[SteCra100]

Hello folks.

I have a column called “DaysBetween” that calculates the days between two date columns in my table:

TableName[StartDate] and TableName[EndDate].

My date range starts from 19/04/2019 to the present day.

I want to create an Average Days measure that calculates how many weekdays it took to complete a task(s), but only display the average. I also just want to record the data from 01/04/2020 onwards.

For example, if 700 tasks have been completed, I want to get the average number of weekdays it took to complete the tasks.

I have created an “AverageDays” measure (see below):

AverageDays = CALCULATE(AVERAGE(TableName[DaysBetween]), all(TableName[EndDate])

However, the above measure calculates the average over my entire dataset, I just want the average to start from 1st April 2020.

Can this be done?

Many thanks,

[amitchandak]

@SteCra100 , refer 2nd page , working date diff of the attached file after signature if that can help

[SteCra100]

Hello @amitchandak 

Many thanks for your quick reply. I'm afriad I can't downlaod your .phix file as it says i'm not on the correct version. Are you able to guide me what measure you used?

Many thanks,

[SteCra100]

Hello @amitchandak 

I have been looking into this more, and I have used the following syntax:

Avg(2) = CALCULATE(AVERAGE(Table1[InsertColumnNameHere]), DATESYTD( Table1[InsertDateColumbNameHere].[Date], "01/04/2020 00:00:00"))

Do you think the above would work? I'm not getting any errors when using the measure and I'm getting, what I belive, is the correct result. I just what to make sure if I'm using DATESYTD function correctly.

Many thanks,

[V-lianl-msft · Accepted solution]

Hi @SteCra100 ,

DATESYTD(<dates> [,<year_end_date>])

You may try:

IF(MAX(Table1[InsertDateColumbNameHere].[Date])>"01/04/2020",
CALCULATE(AVERAGE(Table1[InsertColumnNameHere]), DATESYTD( Table1[InsertDateColumbNameHere].[Date])))

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