Forum Discussion
Helper IVDate difference for working days excluding sundays
Hi labuser1235 ,
In your scenario, we should create a date table like that.
date = ADDCOLUMNS(CALENDARAUTO(),"Sunday",IF(WEEKDAY([Date],2) = 7,1,0))Then we can create a calculated column in our fact table as below.
Column = VAR workingdays = CALCULATE ( COUNTROWS ( 'date' ), FILTER ( 'date', 'date'[Date] > 'Table'[Start Date] && 'date'[Date] <= 'Table'[End Date] && 'date'[Sunday] <> 1 ) ) RETURN SWITCH ( TRUE (), workingdays = 1, "0 to 24 Hrs", workingdays = 2, "24 to 48 Hrs", workingdays = 3, "48 to 72 Hrs", "More that 62 Hrs" )For more details, please check the pbix as attached.
Helper IIIHi labuser1235, sounds like an if formula is needed here.
https://docs.microsoft.com/en-us/dax/if-function-dax
=if ( datediff ([StartDate],[EndDate],HOUR) < 24, "Less Than 24 Hours",
if (datediff ([StartDate],[EndDate],HOUR)>=24 and datediff ([StartDate],[EndDate],HOUR)<48,"24-48 Hours",
if (datediff ([StartDate],[EndDate],HOUR)>=48 and datediff ([StartDate],[EndDate],HOUR)<72,"48-72 Hours","More Than 72 Hours")
Alternatively you could change the datediff to days, which would return whole integers (0,1,2,3 etc) that could be used in a SWITCH function. you can read more, here;
https://docs.microsoft.com/en-us/dax/switch-function-dax
Does that help?
labuser1235javirmerino Thank you so much for your quick response. I want it to ignore Sundays. How can we filter that could you please re-write the formula in that way.
- amitchandak
Super User
Create a date table and use that get days and use that to get your logic.
If you have date table. the You can have new column in your table.
days diff = countx(filter(Date,date[date]>=table[Start_date] && date[date]<=table[Start_date]),If(weekday(date[date])=1,0,1))To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin- labuser1235
Hi amitchandak ,
I have a Dimdate table where weekdays column is available as "Sunday,Monday,Tuesday etc..,".
And both the table are related via Table1 Start date to Dimdate Table date.
However, I want the output column to be as 0 to 24Hrs, 24 to 48Hrs, 48 to 72Hrs, More than 72Hrs.
days diff = countx(filter(Date,date[date]>=table[Start_date] && date[date]<=table[Start_date]),If(weekday(date[date])=1,0,1))
How can we achieve using your formula.
