Calculating the Average between two dates

[Anonymous]

Hi there,

I am going to create a suite of SLA type reports based on a "Incident" data source.

I thought I could create a measure to calculate the average between the 'created date' and the 'resolved date' and then filter for teams and a date range, but I can't seem to get the formula calculation to work.

I assume a measure for each of these is the right way to go about it as the table is loaded with all the dates / times I need.

What would be the best way to calculate this in a measure to use for a line graph?

Thanks all.

[Syndicate_Admin]

Hello I want to get an average frequency between the last 4 dates of sale, I am a beginner and I want to know some method.

Best regards

[amitchandak]

@Anonymous ,

You can create date diff as column

datediff(Table[created date], Table[resolved date], day)

and take average

or a measure

Averagex(Table,datediff(Table[created date], Table[resolved date], day))

How to deal if they are in two table, of you want to measure based on a level

https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Difference-Across/ba-p/934397#M451

[Anonymous]

ah I think I know what is wrong, I need to limit the measure to only be for resolved or closed status also which I have done in a filter on the measure, but now I am stuck with it including weekends.

[Anonymous]

Hi @Anonymous ,

Whether your problem has been resolved? If yes, could you please mark the helpful post as Answered? It will help the others in the community find the solution easily if they face the same problem with you. Thank you.

Best Regards

[Anonymous · Accepted solution]

Hi @Anonymous ,

First, you need to create a Date table and create a calculated column [IsWorkingDay] to recoginize the date is working day or not.

IsWorkingDay = NOT WEEKDAY( 'Date'[Date] ) IN { 1,7 }

Then create a calculated column as below in fact table to get the diff days between created date and resolved date. 

Day Diff =
CALCULATE (
    COUNTROWS ( 'Date' ),
    DATESBETWEEN ( 'Date'[Date], 'Table'[created date], 'Table'[resolved date] - 1 ),
    'Date'[IsWorkingDay] = TRUE,
    ALL ( 'Table' )
)

You can refer the content in the following links for the details.

POWER BI DATEDIFF ONLY WORKING DAYS, HOURS AND SO ON…

Counting working days in DAX

If the above one is not what you want, please provide some sample data in your model(exclude sensitive data) and your expected result with sample or screenshot. Thank you.

Best Regards

[Anonymous]

Thanks for the breakdown, that is really helpful info.