Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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.

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.