Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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 )
5 REPLIES 5
v-danhe-msft
Employee
Employee

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]))
1.PNG
Hope it could help you.
 
Regards,
Daniel He
 
Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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 )

Hi @Anonymous,

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

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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.

WorkingDaysTicketNumberStateNameCreatedOnDateata_CaseClosedDateNZTDifferenceInDays
9CAS-737197-S4D0M1Resolved00:00.000:00.010
2CAS-739097-Y8H4K2Canceled00:00.000:00.02
8CAS-737069-J4R2F3Resolved00:00.000:00.08
28CAS-595511-S2L8S4Resolved00:00.000:00.029
1CAS-574274-L6Z6K8Resolved00:00.000:00.01
8CAS-868319-M5S3D4Resolved00:00.000:00.08
2CAS-595195-Q6K5D9Resolved00:00.000:00.02
3CAS-800943-G2S7W3Resolved00:00.000:00.03
2CAS-418610-R7F3J7Resolved00:00.000:00.02
5CAS-574344-C4F6Z2Resolved00:00.000:00.05
95CAS-739061-C0K4R6Resolved00:00.000:00.097
52CAS-735827-S3V4D8Resolved00:00.000:00.054
9CAS-737475-D5T5M0Resolved00:00.000:00.010
7CAS-738907-N0H7Q0Resolved00:00.000:00.08
1CAS-738006-K2J6Q1Resolved00:00.000:00.01
Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.