Help with DAX for Working Days - Returning no values

[DemoFour]

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. 😊

[v-kkf-msft · Accepted 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.

[DemoFour]

@v-kkf-msft 

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. 

[amitchandak]

@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

[DemoFour]

 @amitchandak I will watch your video, thank you for posting up so quickly. 

[DemoFour]

Thanks for the solution, but this is going to be slower in my report. 

Any other ideas please people?