Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Folks,
I have used this article to implement the DAX solution for my report Counting working days in DAX - SQLBI to count the working days between two dates.
I have a similar table and the dates are on the same row but I am not getting any results. I have this calculated column that is working:
Days Btw Change =
IF(
'Application Status Changes'[Application Sequence] = 1,
BLANK(),
INT('Application Status Changes'[Date Change]) - INT( 'Application Status Changes'[Previous Date] )
)
But this code returns no values:
Working Days Btw Change =
CALCULATE(
COUNTROWS( 'Date' ),
DATESBETWEEN( 'Date'[Date] , 'Application Status Changes'[Date Change] , 'Application Status Changes'[Previous Date] - 1 ),
'Date'[IsWorkingDay] = TRUE,
ALL( 'Application Status Changes' )
)
Any tips as to why this is the case and how to fix it gratefully received. 😊
Solved! Go to Solution.
Hi @DemoFour ,
In the DATESBETWEEN function, the start date should be less than the end date. Therefore you should adjust the position of these two fields.
Try the following formula:
Working Days Btw Change =
CALCULATE(
COUNTROWS( 'Date' ),
DATESBETWEEN( 'Date'[Date] , 'Application Status Changes'[Previous Date] , 'Application Status Changes'[Date Change] - 1 ),
'Date'[IsWorkingDay] = TRUE,
ALL( 'Application Status Changes' )
)
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @DemoFour ,
In the DATESBETWEEN function, the start date should be less than the end date. Therefore you should adjust the position of these two fields.
Try the following formula:
Working Days Btw Change =
CALCULATE(
COUNTROWS( 'Date' ),
DATESBETWEEN( 'Date'[Date] , 'Application Status Changes'[Previous Date] , 'Application Status Changes'[Date Change] - 1 ),
'Date'[IsWorkingDay] = TRUE,
ALL( 'Application Status Changes' )
)
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Good morning,
Of course! In my rush to get this built I missed the simple . . .
Thank you for taking the time to look at this issue and helping to spot this error in order of dates.
I now have days coming through, there are still missing values in the column, but I can now resolve these and review what I am trying to achieve.
I will also review using DATESBETWEEN and try to understand this code more deeply.
This forum is a great place to learn and develop skills, thank you for your time.
Have a great day.
@DemoFour , please find my examples and video for work day if that can help
Not on your columns
a New measure
Work Day = COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(Min(Table[Start Date]),Max(Table[End Date])),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1))
As a new column
Work Day = COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(Table[Start Date],Table[End Date]),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1))
Video : https://www.youtube.com/watch?v=Qv4wT8_P-AA
Thanks for the solution, but this is going to be slower in my report.
Any other ideas please people?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |