cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jessi82315
Helper I
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. 

NAABCDEF
1Date ReceivedPart NumberDescriptionDate Orderedindex for Date orderedworking days between "Date Ordered" within each "Part Number"
212/10/2021AJuice12/1/202111
312/10/2021AJuice12/1/202121
412/11/2021BChocolate12/1/202112
512/11/2021BChocolate11/30/202122
612/12/2021CCake11/18/202119
712/12/2021CCake11/8/202129
812/13/2021DCream Puff12/1/2021123
912/13/2021DCream Puff11/1/2021223

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


Any help is appreciated!!

Thanks

Jessie

1 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:

vchenwuzmsft_0-1640248824802.png

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.

 

View solution in original post

4 REPLIES 4
amitchandak
Super User
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
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.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
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 !!

Hi @amitchandak ,

 

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

I tried it but it still picks up the weekends. 

 

jessi82315_0-1640114568265.pngjessi82315_1-1640114594508.png

 

 

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:

vchenwuzmsft_0-1640248824802.png

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.

 

@v-chenwuz-msft 

 

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

 

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

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

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

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

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