Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I'd like to be able to count the changes (add/removed) of SKUs with each BOM per day. Unfortunately, a simple count might miss where the same number of SKUs are removed as have been added. I've been playing around with EXCEPT and DISTINCTs but I can get them to count up into a table. I'll admit, I'm not that advanced of a DAX user.
I appreciate any and all suggestions! Thank you!
(apologies for the formatting)
Example Source Data
Date | BOM | SKU | Cost |
1/1/2000 | 10001 | AAAA | $ 1.00 |
1/1/2000 | 10001 | AAAB | $ 2.00 |
1/1/2000 | 10001 | AAAC | $ 3.00 |
1/1/2000 | 10002 | BBBA | $ 5.00 |
1/1/2000 | 10002 | BBBB | $ 6.00 |
1/1/2000 | 10002 | BBBC | $ 7.00 |
1/2/2000 | 10001 | AAAA | $ 1.00 |
1/2/2000 | 10001 | AAAB | $ 2.00 |
1/2/2000 | 10001 | AAAD | $ 4.00 |
1/2/2000 | 10002 | BBBA | $ 5.00 |
1/2/2000 | 10002 | BBBB | $ 6.00 |
1/2/2000 | 10002 | BBBC | $ 7.00 |
1/2/2000 | 10003 | DDDA | $ 10.00 |
1/2/2000 | 10003 | DDDC | $ 12.00 |
1/2/2000 | 10003 | DDDD | $ 13.00 |
1/3/2000 | 10002 | BBBB | $ 6.00 |
1/3/2000 | 10002 | BBBC | $ 7.00 |
1/3/2000 | 10002 | BBBD | $ 8.00 |
1/3/2000 | 10003 | DDDB | $ 11.00 |
1/3/2000 | 10003 | DDDD | $ 13.00 |
Desired output data
Date | BOM | # of SKU | # of SKUs added | # of SKUs Removed | Total Cost | Cost Diff |
1/1/2000 | 10001 | 3 | 0 | 0 | $ 6.00 | $ - |
1/1/2000 | 10002 | 3 | 0 | 0 | $ 18.00 | $ - |
1/2/2000 | 10001 | 3 | 1 | 1 | $ 7.00 | $ 1.00 |
1/2/2000 | 10002 | 3 | 0 | 0 | $ 18.00 | $ - |
1/2/2000 | 10003 | 3 | 3 | 0 | $ 35.00 | $ - |
1/3/2000 | 10002 | 3 | 1 | 1 | $ 21.00 | $ 3.00 |
1/3/2000 | 10003 | 2 | 1 | 2 | $ 24.00 | $ (11.00) |
Solved! Go to Solution.
Hi @Ayric2
1. Place Date and BOM in a table visual (both with Don't summarize)
2. Create this measure and place it in the visual for the number of SKUs added. You can follow a similar reasoning to create a measure that shows the removed SKUs:
# SKUs added =
VAR previousDate_ =
CALCULATE (
MIN ( Table1[Date] ),
FILTER ( ALL ( Table1[Date] ), Table1[Date] < SELECTEDVALUE ( Table1[Date] ) )
)
VAR firstDateInT_ = MINX ( ALL ( Table1[Date] ), Table1[Date] )
RETURN
IF (
SELECTEDVALUE ( Table1[Date] ) = firstDateInT_,
0,
COUNTROWS (
EXCEPT (
DISTINCT ( Table1[SKU] ),
CALCULATETABLE ( DISTINCT ( Table1[SKU] ), Table1[Date] = previousDate_ )
)
) + 0
)
Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hi @Ayric2
1. Place Date and BOM in a table visual (both with Don't summarize)
2. Create this measure and place it in the visual for the number of SKUs added. You can follow a similar reasoning to create a measure that shows the removed SKUs:
# SKUs added =
VAR previousDate_ =
CALCULATE (
MIN ( Table1[Date] ),
FILTER ( ALL ( Table1[Date] ), Table1[Date] < SELECTEDVALUE ( Table1[Date] ) )
)
VAR firstDateInT_ = MINX ( ALL ( Table1[Date] ), Table1[Date] )
RETURN
IF (
SELECTEDVALUE ( Table1[Date] ) = firstDateInT_,
0,
COUNTROWS (
EXCEPT (
DISTINCT ( Table1[SKU] ),
CALCULATETABLE ( DISTINCT ( Table1[SKU] ), Table1[Date] = previousDate_ )
)
) + 0
)
Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Worked like a charm... exactly what I needed. Thank you so much for your help!
2nd question: Now I need to sum up the positive and negative changes in a binned by week/month date hierarchy in a visualization. It's only counting the positives and the negatives show as zero. What needs to be changed to make this behave as I need it to?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |