cancel
Showing results for
Did you mean:
Helper I

## calculate working days in rows

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

1 ACCEPTED SOLUTION
Community Support

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',
&& [Part Number] = EARLIER( 'Table'[Part Number] )
),
[Date Ordered]
)
VAR _max =
MAXX(
FILTER(
'Table',
&& [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.

4 REPLIES 4
Super User

@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

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

Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!
Helper I

Hi @amitchandak ,

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

I tried it but it still picks up the weekends.

Community Support

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',
&& [Part Number] = EARLIER( 'Table'[Part Number] )
),
[Date Ordered]
)
VAR _max =
MAXX(
FILTER(
'Table',
&& [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.

Helper I

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

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

Announcements

#### The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

#### Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!