calculate working days in rows

[jessi82315]

Hello all!

Please see below "Inventory table". Data in black is the exisiting data. Well, the "index for Date ordered" was added to eliminate from the previous dataset and Column F is what I want to achieve. I want to calculate the working days between "Date Ordered" for each part number. 

for example, in excel: for P/N A -> I enter =NETWORKDAYS(D2,D3) to get net workdays betweens "Date Ordered" for "Part Number" A. 

NA	A	B	C	D	E	F
1	Date Received	Part Number	Description	Date Ordered	index for Date ordered	working days between "Date Ordered" within each "Part Number"
2	12/10/2021	A	Juice	12/1/2021	1	1
3	12/10/2021	A	Juice	12/1/2021	2	1
4	12/11/2021	B	Chocolate	12/1/2021	1	2
5	12/11/2021	B	Chocolate	11/30/2021	2	2
6	12/12/2021	C	Cake	11/18/2021	1	9
7	12/12/2021	C	Cake	11/8/2021	2	9
8	12/13/2021	D	Cream Puff	12/1/2021	1	23
9	12/13/2021	D	Cream Puff	11/1/2021	2	23

But I'm not sure how to do this in Power BI DAX...!

Any help is appreciated!!

Thanks

Jessie

[amitchandak]

@jessi82315 , Try a new column like

Work Day =
var _min = minx(filter(Table, [Part Number] = earlier([Part Number])) ,[Date Ordered])
var _max = minx(filter(Table, [Part Number] = earlier([Part Number])) ,[Date Ordered])
return
COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(_min,_max),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1))

This is based on fact that both rows has same datediff.

[jessi82315]

Hi @amitchandak ,

for the variable _max, should it be MAXX instead of MINX??

I tried it but it still picks up the weekends. 

[v-chenwuz-msft · Accepted solution]

Hi @jessi82315 ,

It is possible that the condition is missing. To avoid duplication of part numbers, we need to add an additional condition.

code:

working days =
VAR _min =
    MINX(
        FILTER(
            'Table',
            [Date Received] = EARLIER( 'Table'[Date Received] )
                && [Part Number] = EARLIER( 'Table'[Part Number] )
        ),
        [Date Ordered]
    )
VAR _max =
    MAXX(
        FILTER(
            'Table',
            [Date Received] = EARLIER( 'Table'[Date Received] )
                && [Part Number] = EARLIER( 'Table'[Part Number] )
        ),
        [Date Ordered]
    )
VAR _c =
    FILTER( CALENDAR( _min, _max ), WEEKDAY( [Date], 2 ) <= 5 )
RETURN
    COUNTROWS( _c )

result:

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jessi82315]

@v-chenwuz-msft 

Thank you!!! It is working great now!! 😃

Thanks again for both of your help :)!!!! Merry Christmas!!🎄