Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Hi @Anonymous,
Based on my test, the problem may due to below code:
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..
Hi @Anonymous,
Could you please offer me a sample data to have a test if possible?
Regards,
Daniel He
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 |
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
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |