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
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.