Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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 , 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.
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.
Thank you!!! It is working great now!! 😃
Thanks again for both of your help :)!!!! Merry Christmas!!🎄
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |