Calculating number of working days between two dates using Direct query mode

[Anonymous]

Hi everyone,

I am trying to work out the total working days between two dates in my fact table where i have a Holday column & working day column in my dim_date table.

I am using Direct Query Mode and am aware of the limitations in DAX.

I am comfortable with eithe a calculated column or measure which will work this out as the business operates on Working days and not a standard datediff calculation. I have used the below dax on the query in Import mode but it does not work when in direct query mode.

Queue Duration (Working Days) =

VAR currentEnteredQueueDate = Queue[Entered Queue Date]

VAR currentNextEventDate = Queue[Closed Date]

VAR queueDurationWorkingDays =

CALCULATE (

COUNTROWS ( dim_date ),

FILTER (

ALL ( dim_date[Date], dim_date[Holiday Flag], dim_date[Week Day Flag] ),

dim_date[Week Day Flag] = "Y"

&& dim_date[Holiday Flag] = "N"

&& dim_date[Date] > currentEnteredQueueDate

&& dim_date[Date] <= currentNextEventDate

)

)

RETURN

IF ( ISBLANK ( queueDurationWorkingDays ), 0, queueDurationWorkingDays )

[v-danhe-msft]

Hi @Anonymous,

Based on my test, the problem may due to below code:

VAR currentEnteredQueueDate = Queue[Entered Queue Date]

VAR currentNextEventDate = Queue[Closed Date]

May be you could modify it  as below code:

VAR currentEnteredQueueDate =

CALCULATE(MAX(Queue[Entered Queue Date]))

VAR currentNextEventDate =

CALCULATE(MAX(Queue[Closed Date]))

Hope it could help you.

Regards,

Daniel He

[Anonymous]

Thanks @v-danhe-msft, added the below code into a measure & no luck! All i get is 0. I am referencing my date table to include the Holiday & weekends though..

Working Days =

VAR Currentcreateddate =

CALCULATE ( MAX ( fact_CRM_Incident[Created On Date] ) )

VAR Currentcloseddate =

CALCULATE ( MAX ( fact_CRM_Incident[Closed on Date] ) )

VAR Businessdays =

CALCULATE (

COUNTROWS( fact_CRM_Incident),

FILTER (

ALL ( dim_date ),

dim_date[holiday_flag] = "N"

&& dim_date[week_day_flag] = "Y"

&& dim_date[Date] > Currentcreateddate

&& dim_date[date] <= Currentcloseddate

)

)

RETURN

IF ( ISBLANK ( Businessdays ), 0, Businessdays )

[v-danhe-msft]

Hi @Anonymous,

Could you please offer me a sample data to have a test if possible?

Regards,

Daniel He

[Anonymous]

Hi Daniel, 

This is a small sample of what i want my result to look like, You can use this as a sample by working the difference of createdondate & closeddate.

WorkingDays	TicketNumber	StateName	CreatedOnDate	ata_CaseClosedDateNZT	DifferenceInDays
9	CAS-737197-S4D0M1	Resolved	00:00.0	00:00.0	10
2	CAS-739097-Y8H4K2	Canceled	00:00.0	00:00.0	2
8	CAS-737069-J4R2F3	Resolved	00:00.0	00:00.0	8
28	CAS-595511-S2L8S4	Resolved	00:00.0	00:00.0	29
1	CAS-574274-L6Z6K8	Resolved	00:00.0	00:00.0	1
8	CAS-868319-M5S3D4	Resolved	00:00.0	00:00.0	8
2	CAS-595195-Q6K5D9	Resolved	00:00.0	00:00.0	2
3	CAS-800943-G2S7W3	Resolved	00:00.0	00:00.0	3
2	CAS-418610-R7F3J7	Resolved	00:00.0	00:00.0	2
5	CAS-574344-C4F6Z2	Resolved	00:00.0	00:00.0	5
95	CAS-739061-C0K4R6	Resolved	00:00.0	00:00.0	97
52	CAS-735827-S3V4D8	Resolved	00:00.0	00:00.0	54
9	CAS-737475-D5T5M0	Resolved	00:00.0	00:00.0	10
7	CAS-738907-N0H7Q0	Resolved	00:00.0	00:00.0	8
1	CAS-738006-K2J6Q1	Resolved	00:00.0	00:00.0	1

[Anonymous]

Used the below sql query to work out the working days & regular days calculation

i have worked out the working days & regular days difference calculation using the below sql code.
SELECT    SUM( CASE WHEN holiday_flag = 'N' THEN 1 ELSE 0 END ) AS WorkingDays,
        SubQuery.*
FROM    (
 
        SELECT    top 1000 fact_CRM_Incident.TicketNumber ,
                fact_CRM_Incident.StateName,
                CreatedOnDate,
                fact_CRM_Incident.ata_CaseClosedDateNZT,
 
                CASE WHEN ( fact_CRM_Incident.StateName='Resolved' OR fact_CRM_Incident.StateName = 'Canceled' )
                            AND fact_CRM_Incident.ata_CaseClosedDateNZT IS NOT NULL
                    THEN DATEDIFF(day, fact_CRM_Incident.CreatedOnDate,ata_CaseClosedDateNZT )
                ELSE
                    DATEDIFF( day, fact_CRM_Incident.CreatedOnDate , GETDATE() )
                END AS DifferenceInDays
        FROM    fact_CRM_Incident
        --WHERE    IncidentId = '5D566EEB-717D-E711-8126-70106FA11A21'
) SubQuery CROSS JOIN dim_date --ON 1=1
WHERE    calendar_date > CreatedOnDate AND calendar_date  <= ata_CaseClosedDateNZT
GROUP BY TicketNumber,
        StateName,
        CreatedOnDate,
        ata_CaseClosedDateNZT,
        DifferenceInDays