Diff. date without weekend´s hours

[Fidzi8]

Hi,

I need advice to adjust the formula.

I need column Diff. order (h) without weekend´s hours.

Thank you
Ondřej

[v-luwang-msft · Accepted solution]

Hi @Fidzi8  

You cold try  the following  Steps:

Step 1:create date table:

Dates = CALENDAR("2021/1/1","2021/12/31")

Step 2:create new table base on dates:

date1 =
GENERATE (
    CALENDARAUTO (),
    VAR yyyy =
        YEAR ( [Date] )
    VAR mmmm =
        MONTH ( [Date] )
    RETURN
        ROW (
            "Year", yyyy,
            "Month", FORMAT ( [Date], "mmmm" ),
            "Month2", mmmm,
            "Week1", FORMAT ( [Date], "dddd" ),
            "Week2", WEEKDAY ( [Date] ),
            "weekday", NOT WEEKDAY ( [Date] ) IN { 1, 7 }
        )
)

Step 3, Create new column in main database table:

difforder =
VAR day1 =
    CALCULATE (
        COUNTROWS ( 'date1' ),
        DATESBETWEEN ( date1[Date], OTTO[Time of order], OTTO[Delivery(To)+ 1h] ),
        date1[weekday] <> TRUE,
        ALL ( OTTO )
    )
VAR day2 =
    IF ( day1 = BLANK (), 0, day1 )
VAR day3 =
    DATEDIFF ( OTTO[Time of order], OTTO[Delivery(To)+ 1h], HOUR ) - day2 * 24
RETURN
    day3

Click here to download pbix if you need.

Best Regard

Lucien Wang

[amitchandak]

@Fidzi8 , one of the way is

a new column as 

Work Day = COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(max(Table[time to order]),Table[Delivery To]),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1))

Another is refer to these link, business hours

https://community.powerbi.com/t5/Desktop/Calculating-Working-hours/td-p/374255

https://exceleratorbi.com.au/calculating-business-hours-using-dax/