Forum Discussion
Calculating the Average between two dates
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…
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 ,
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
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.
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…
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
Thanks for the breakdown, that is really helpful info.
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