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.
Hello,
I have a starting date and an ending date and I need to count only the working days in between. In other words, the date difference (DATEIFF) between the ending and the starting without taking into consideration the weekends
ReleaseDt ClosedDt DaysDiff(Excludeweekends)
How can I achieve this?
Thank you,
Hi,
This maybe the solution:
https://www.youtube.com/watch?v=bs3yzmf9elAhttps://www.youtube.com/watch?v=bs3yzmf9elA
Regards,
Hi there.
These solved threads might help you:
https://community.powerbi.com/t5/Desktop/Calculate-working-days-between-2-dates/td-p/230349
https://community.powerbi.com/t5/Desktop/DATEDIFF-Working-Days/td-p/130662
Regards.
Thanks.
But here they suggested create a Date Table for Isweekday flag.
I worked on almost 10 years data so, cant possible to create Date Table with Isweekday flag ??
Hi @ssvr,
Yes. It is supported to create a calendar table with more than 10 years.
Regards,
Yuliana Gu
Hi @Yuliana,
Thanks.
But its difficult to add 10 years canlender and we have to add for future dates also right ?
can you suggest any alternative for this requirement
Hi @ssvr,
You can create calendar table with CALENDAR() function like this:
Dim Table=CALENDAR(DATE(2008,1,1),DATE(2018,12,31))
Regards,
Yuliana Gu
Thanks @v-yulgu-msft
I created a Date table:
Dim Table=CALENDAR(DATE(2008,1,1),DATE(2018,12,31))
I created a new column with is working day or not
is work day = SWITCH(WEEKDAY([Date]),1,0,7,0,1)
Now I want to create "DateDifference" Column with Createddate & Closeddate (I want to know day diffarence b/w these two dates excluding weekends)
CreatedDate ClosedDate DateDiffarence
Please guide me how to get this done
Could you please resolve my issue.
I created a Date table:
Dim Table=CALENDAR(DATE(2008,1,1),DATE(2018,12,31))
I created a new column with is working day or not
is work day = SWITCH(WEEKDAY([Date]),1,0,7,0,1)
Now I want to create "DateDifference" Column with Createddate & Closeddate (I want to know day diffarence b/w these two dates excluding weekends)
CreatedDate ClosedDate DateDiffarence
As a measure (recommended, you'd have to make a strong case to make this a calculated column):
[Date Difference Measure] = VAR CreateDate = MIN ( TableName[CreatedDate] ) VAR CloseDate = MIN ( TableName[ClosedDate] ) VAR CalendarNoWeekends = FILTER ( CalendarTable, CalendarTable[Date] >= CreateDate && CalendarTable[Date] <= CloseDate && CalendarTable[Is Working Day] = "Yes" ) RETURN COUNTROWS ( CalendarNoWeekends )
As a calculated column (again, make sure you need to add a non-compressed column to your data model before going this way):
Date Difference Calculated Column = VAR CreateDate = TableName[CreatedDate] VAR CloseDate = TableName[ClosedDate] VAR CalendarNoWeekends = FILTER ( CalendarTable, CalendarTable[Date] >= CreateDate && CalendarTable[Date] <= CloseDate && CalendarTable[Is Working Day] = "Yes" ) RETURN COUNTROWS ( CalendarNoWeekends )
Only difference is you already have a row context, so you don't need to wrap the start and end dates in a MIN() function.
Hope this helps.
Did you ever find an answer to this? I have been struggling for a few days trying to accomplish the same measure.
In my case:
Call Close - Call open then filter out work days from a isweekday column on my date table. I can't seem to get any of these suggestions to work.
Thanks!
Something like this:
[Date Diff Measure] = VAR Created = MAX ( Table[CreatedDate] ) VAR Closed = MAX ( Table[ClosedDate] ) RETURN CALCULATE ( SUM ( 'Dim Table'[is work day] ), FILTER ( ALL ( 'Dim Table'[DateColumn] ), 'Dim Table'[DateColumn] >= Created && 'Dim Table'[DateColumn] <= Closed ) )
No Data shown in the Output column
@Anonymous
Here SLA is measure !
Can you fix this error:
Logical expressions that are added to CALCULATE() must be of the format Table[Column] = static expression.
If you want to do a more advanced logical expression, such as the following:
Table[Column] = [Measure]
[Measure] = static expression
[Measure] = [another Measure]
then you need to use FILTER() inside of CALCULATE()
Something like this:
[Count of SLA] = CALCULATE ( DISTINCTCOUNT ( Dates[CreateDt].[Date] ), FILTER ( VALUES(Dates[CreateDt]), [SLA] = "Met SLA" ) )
The FILTER() parameter says this:
"Look at the distinct list of dates in the CreateDt column, based on the current filter context (as determined by slicers, etc.) Go through that list line by line and evaluate the [SLA] measure. Only keep those dates where [SLA] = "Met SLA"
Now you have a (likely) smaller list of values from the Dates[CreateDt] column. That is the additional filter that is taken into account when CALCULATE performs the calculation...in this case counting the distinct number of values in the Dates[CreateDt] column.
@Anonymous
Thanks ChrisHaas.
Its working great.
Can you fix the DateDiff excluding weekends DAX also
@Anonymous
some error in DAX
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |