Cost Per Unit Variances

[Anonymous]

I want show the unit cost variance from one month to the next - but if there is a blank month I want it to skip that month and go to the next...Ex: the 2nd item on the list - I want it to show the variance from Feb to Mar, Mar to Apr, Apr to July, July to Sep, etc. Can someone please help me?

[v-jiascu-msft]

Hi @Anonymous,

Can you share a sample of the original data, please?

Best Regards,
Dale

[Anonymous]

Here's an example of the original data

Period	Part	Qty	Burden Actual Cost	Labor Actual Cost	Labor Var Inventory Value	Mtl Actual Cost
3/31/17	10246099	48	1,241.10	74.18	(67.68)	2,376.04
2/28/17	10246099	60	1,241.10	74.18	(84.60)	2,616.23
2/28/17	10246099	96	4,451.41	266.07	(135.36)	4,755.90
4/30/17	10246099	240	6,224.41	372.04	(338.40)	11,422.99
7/31/17	10246099	204	4,833.00	264.94	(320.28)	9,348.57
9/30/17	10246099	84	2,404.90	150.63	(131.88)	3,975.00
10/31/17	10246099	204	5,090.76	279.07	(320.28)	9,371.75
11/30/17	10246099	132	4,124.16	226.09	(207.24)	6,529.27

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

I don't know how to calculate the cost. But you can change it yourself. Please download the demo in the attachment.

Measure 3 =
VAR lastMonth =
    CALCULATE (
        MONTH ( MAX ( 'Table1'[Period] ) ),
        FILTER ( ALL ( 'Calendar'[Date] ), 'Calendar'[Date] < MIN ( 'Calendar'[Date] ) )
    )
VAR costLastMonth =
    CALCULATE (
        DIVIDE (
            SUMX (
                'Table1',
                'Table1'[Burden Actual Cost] + 'Table1'[Labor Actual Cost]
                    + 'Table1'[Labor Var Inventory Value]
                    + Table1[Mtl Actual Cost]
            ),
            SUM ( Table1[Qty] )
        ),
        FILTER ( ALL ( 'Calendar'[Date] ), MONTH ( 'Calendar'[Date] ) = lastMonth )
    )
VAR costThisMonth =
    DIVIDE (
        SUMX (
            'Table1',
            'Table1'[Burden Actual Cost] + 'Table1'[Labor Actual Cost]
                + 'Table1'[Labor Var Inventory Value]
                + Table1[Mtl Actual Cost]
        ),
        SUM ( Table1[Qty] )
    )
RETURN
    IF ( ISBLANK ( costThisMonth ), BLANK (), costThisMonth - costLastMonth )

Best Regards,
Dale