cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hmedinaa Frequent Visitor
Frequent Visitor

Re: DATEDIFF Working Days

I can no see where is the calculation to find the weeks days with the scenario using date from 2017/1/1 to 2017/3/31.

bengisby Regular Visitor
Regular Visitor

Re: DATEDIFF Working Days

Hello @v-huizhn-msft, this is a great solution and I"m close to solving my problem, but how can I insert the measure result as a value in the new column? I need the business days value for EACH ROW of data, not as a single summarized measure. Any ideas? 

clubspec Frequent Visitor
Frequent Visitor

Re: DATEDIFF Working Days

Hi,

 

I stuck at this step too.  How do I use this measure result in calculating the column?

 

Thank you your help in advance.

Archie1 Frequent Visitor
Frequent Visitor

Re: DATEDIFF Working Days

Hi @prakashacharya,


I am not sure if you have got your answer or not.

Here is the answer,

 

If you are planning to get total no. of days then you have to use COUNTROWS or SUM function in your main field.

 

 

for example: Nofworkingdays_Month = CALCULATE(COUNTROWS('month'), Dimtable[if working day] =1)

 

where, Month  = DATESINPERIOD('Calendar'[Date],[ThisMonth],1,MONTH)

         ThisMonth = DATE(YEAR([Today]), MONTH([Today]),1)

        Today = DATE(YEAR(Now()), MONTH(NOW()), DAY(NOW()))

 

For more details refer to this link: https://blog.andredevelopment.com/post/power-bi-desktop-fiscal-comparison-part-1-setting-up-for-the-...

 

Cheers, Archie

Archie1 Frequent Visitor
Frequent Visitor

Re: DATEDIFF Working Days

Hi @prakashacharya  and @hmedinaa

 

Please find my soultion below for finding no. of working days excluding holidays & weekends.

 

WorkingDays_FiscalMTH = CALCULATE((sum('Calendar'[.IsWorkingDay])+1),ALLEXCEPT('Calendar','Calendar'[fiscalYear],'Calendar'[fiscalQuarter],'Calendar'[fiscalMonth]))

 

OR 

Use

WorkingDays_MTH = CALCULATE((sum('Calendar'[.IsWorkingDay])+1),ALLEXCEPT('Calendar','Calendar'[Year],'Calendar'[Quarter],'Calendar'[Month]))

 

Note: Assuming that you already had a calendar table with Fiscal year, Fiscal Quarter & Fiscal Month.

 Hope this helps!! 

Cheers,

Archie

Anonymous
Not applicable

Re: DATEDIFF Working Days

Good morning,

 

I've followed these instructions for calculate the working days. In fact, I have a table with the saturdays, sundays and holidays. But I'm not getting what I want. Could you help me?

 

Also, I have a table like this:

IDStart DateFinal DateDATEDIFFDATEDIFF1
12301/01/201802/01/20181?
12405/01/201810/01/20185?
12515/01/201801/02/201817?
12618/01/201820/01/20182?

 

I got a DATEDIFF between dates but I want the DATEDIFF discounts the saturdays, sundays and holidays. I thoght about calculate how many saturdays, sundays and holidays are between start and final date and then rest it in DATEDIFF column. But I don't know how I should say it to PowerBI.

 

Regards

Anonymous
Not applicable

Re: DATEDIFF Working Days


@v-huizhn-msft wrote:

Hi @Benjamin_500,

First you should create a holiday table including all holiday form Delivery start date to Today. Then create relationship between holiday table and your 'Repot' table. 

I try to reproduce your scenario using date from 2017/1/1 to 2017/3/31.

Search the holiday days for 2017, I use "List of federal Public Holidays of USA in 2017", and type it in Power BI table as followings.

2.PNG

Create relationship between Holiday table and Date Table.

1.PNG

Use WEEKDAY function to get weekdays, use RELATED function to get holidays. Use if function to decide if the day is work day.

 

WeekDay = WEEKDAY(DateTable[Date])

Holiday = RELATED(Holiday[Holiday])

If work day = IF(OR(DateTable[WeekDay]=1,DateTable[WeekDay]=7),0,IF(ISBLANK(DateTable[Holiday]),1,0))

 

3.png

The 1 stands for the day is work day.

Finally, create a measure to calculate the number od holidays. And create a card visual to display the result.

Total work days = CALCULATE(COUNT(DateTable[Date]),FILTER(DateTable,DateTable[If work day]=1))

4.PNG

If you have other issues, please let me know.

Best Regards,
Angelia


Hi @v-huizhn-msft Angelia,

I'm having the same issue, your solution is great, however this will give the total result for an year. how to obtain work days per month basis. i.e. 

January  20

Feb        19 

Mar        21    likwise. I'm bit strugglling with dax code. Thanks again. Keep up the good work!

 

 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 45 members 821 guests
Please welcome our newest community members: