Hi there,
I have TSQL function that calculates Weekdays Hours and I tried to convert it to DAX but I fall short
Here is the TSQL function my question how do I convert it to DAX ?
CREATE FUNCTION [dbo].[fn_getWeekDaysHour] (@StartDate DATETIME, @EndDate DATETIME)
RETURNS DECIMAL(6,2)
AS
BEGIN
DECLARE @TotalHour DECIMAL(6,2)
SET @TotalHOur=
(
( DATEDIFF(MINUTE, @StartDate, @EndDate)
- ( DATEDIFF(wk, @StartDate,@EndDate)*(2*24*60)
-- End on Sunday
-(CASE WHEN DATEPART(dw, @EndDate) = 1 THEN 24.0*60-DATEDIFF(MINUTE,CONVERT(DATE,@EndDate),@EndDate) ELSE 0 END)
-- Start on Saturday
-(CASE WHEN DATEPART(dw, @StartDate) = 7 THEN DATEDIFF(MINUTE,CONVERT(DATE,@StartDate),@StartDate) ELSE 0 END)
-- End on Saturday
+(CASE WHEN DATEPART(dw, @EndDate) = 7 THEN DATEDIFF(MINUTE,CONVERT(DATE,@EndDate),@EndDate) ELSE 0 END)
-- Start on Saturday
+(CASE WHEN DATEPART(dw, @StartDate) = 1 THEN 24.0*60-DATEDIFF(MINUTE,CONVERT(DATE,@StartDate),@StartDate) ELSE 0 END)
))/60
)
RETURN @TotalHour
END
--- To test
Select dbo.fn_getWeekDaysHour('2022-03-20 12:06:58 AM','2022-03-25 08:48:34 AM') -- 104.80 Hrs
