How to exclude weekends (Sat and Sun) while calculating number of days in query editor

[Anonymous]

Hi All,

Need your help in calculating number of days from 2 dates exluding Saturday and Sunday in query editor.

Thanks and Regards

Abhijit

[v-chuncz-msft · Accepted solution]

@Anonymous,

You may refer to the following post.

https://community.powerbi.com/t5/Desktop/How-to-calculate-net-working-days-Step-by-Step/td-p/232478

[JoHo_BI]

Add a calculated column to your date table to return 1 for a weekday and 0 for a weekend, then add the new calculated column based on the dates you provide.

Hope that helps! 

[Anonymous]

Thanks, Can you please help me with the logic.

Regards

Abhijit Mishra

[JoHo_BI]

Assuming you have a date table, add a calculated column:

IsWeekday = WEEKDAY('Date'[Date],2) <= 5

Then in your measures you can use this as logic, for example: 

Sales = CALCULATE(SUM(Sales[SalesAmount]), 'Date'[IsWeekday] = TRUE())

[Analitika]

It doesn't exclude a gap.

[Analitika]

I also tried this  but doesn't work if we require use x-axis only continuous.