Counting Changes in SKU by BOM per Day

[Ayric2]

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)

[AlB · Accepted 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 

[Ayric2]

Worked like a charm... exactly what I needed. Thank you so much for your help!

[Ayric2]

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?