Date difference for working days excluding sundays

[labuser1235]

Hi All,

I would like to created a calculated column where it calculates date difference between two columns and return if it falls under 0-24 hrs or 24-48 hrs or 48-72 hrs or above 72hrs. Excluding only Sundays.

Table:

Start Date	Fruit Name	Type	Price Sold	End Date
01-31-2020	Orange	Type-2	7	02-01-2020
01-31-2020	Apple	Type-3	8	02-01-2020
01-31-2020	Grapes	Type-1	2	02-03-2020
01-31-2020	Orange	Type-1	5	02-03-2020
01-31-2020	Orange	Type-3	10	02-03-2020
01-31-2020	Apple	Type-1	3	02-01-2020
01-31-2020	Grapes	Type-2	4	02-01-2020
01-31-2020	Grapes	Type-3	6	02-03-2020
01-31-2020	Orange	Type-1	5	01-31-2020
01-31-2020	Orange	Type-2	7	01-31-2020
01-31-2020	Apple	Type-3	8	01-31-2020
01-31-2020	Grapes	Type-1	2	01-31-2020
01-30-2020	Orange	Type-1	5	02-03-2020
01-30-2020	Orange	Type-3	10	02-03-2020
01-30-2020	Apple	Type-1	3	02-03-2020
01-30-2020	Grapes	Type-2	4	02-01-2020
01-30-2020	Grapes	Type-3	6	02-01-2020
01-30-2020	Orange	Type-1	5	02-01-2020

Expected Output:

Start Date	Fruit Name	Type	Price Sold	End Date	Expected Output
01-31-2020	Orange	Type-2	7	02-01-2020	24 to 48 Hrs
01-31-2020	Apple	Type-3	8	02-01-2020	24 to 48 Hrs
01-31-2020	Grapes	Type-1	2	02-03-2020	48 to 72 Hrs
01-31-2020	Orange	Type-1	5	02-03-2020	48 to 72 Hrs
01-31-2020	Orange	Type-3	10	02-03-2020	48 to 72 Hrs
01-31-2020	Apple	Type-1	3	02-01-2020	24 to 48 Hrs
01-31-2020	Grapes	Type-2	4	02-01-2020	24 to 48 Hrs
01-31-2020	Grapes	Type-3	6	02-03-2020	48 to 72 Hrs
01-31-2020	Orange	Type-1	5	01-31-2020	0 to 24 Hrs
01-31-2020	Orange	Type-2	7	01-31-2020	0 to 24 Hrs
01-31-2020	Apple	Type-3	8	01-31-2020	0 to 24 Hrs
01-31-2020	Grapes	Type-1	2	01-31-2020	0 to 24 Hrs
01-30-2020	Orange	Type-1	5	02-03-2020	More than 72 Hrs
01-30-2020	Orange	Type-3	10	02-03-2020	More than 72 Hrs
01-30-2020	Apple	Type-1	3	02-03-2020	More than 72 Hrs
01-30-2020	Grapes	Type-2	4	02-01-2020	48 to 72 Hrs
01-30-2020	Grapes	Type-3	6	02-01-2020	48 to 72 Hrs
01-30-2020	Orange	Type-1	5	02-01-2020	48 to 72 Hrs

[v-frfei-msft · Accepted solution]

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.

[javirmerino]

I've just realised you want Sundays excluded also. Do you have a Date Dimensions table in use?

If so, you can add an additional column, IsWeekDay, using the SWITCH function;

IsWeekDay =
SWITCH (
    WEEKDAY ( [Date] ),
    1, 0,
    1
)

 This basically says if weekday is 1 (Sunday) then 0 (false), else 1 (True), which can be referenced in a second measure;

=
CALCULATE (
    SUM ( DateTable[IsWorkday] ),
    DATESBETWEEN (
        DateTable[Date],
        'Source'[StartDate],
        'Source'[EndDate]
    )
)

Finally, this can be referenced in the original If formula or a new SWITCH to convert the integers into your string for binning.

I hope that helps a little more for you.

[javirmerino]

Hi @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?

[labuser1235]

@javirmerino 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]

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.

[amitchandak]

On top of that, you can write another column or in same column taking that as var

Switch(true(),
table[days diff] <=1 , "0 to 24Hrs",
table[days diff] >1 && table[days diff] <=2 , "24 to 48Hrs",
table[days diff] >2 && table[days diff] <=3, "48 to 72Hrs",
"More than 72Hrs")