Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Cost Per Unit Variances

 

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?

 

Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    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 )

    Cost-Per-Unit-Variances

     

     

    Best Regards,
    Dale

3 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    Hi Anonymous,

     

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

     

     

    Best Regards,
    Dale

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Here's an example of the original data

      PeriodPartQtyBurden Actual CostLabor Actual CostLabor Var Inventory ValueMtl Actual Cost
      3/31/171024609948                     1,241.10                       74.18                                      (67.68)              2,376.04
      2/28/171024609960                     1,241.10                       74.18                                      (84.60)              2,616.23
      2/28/171024609996                     4,451.41                     266.07                                    (135.36)              4,755.90
      4/30/1710246099240                     6,224.41                     372.04                                    (338.40)           11,422.99
      7/31/1710246099204                     4,833.00                     264.94                                    (320.28)              9,348.57
      9/30/171024609984                     2,404.90                     150.63                                    (131.88)              3,975.00
      10/31/1710246099204                     5,090.76                     279.07                                    (320.28)              9,371.75
      11/30/1710246099132                     4,124.16                     226.09                                    (207.24)              6,529.27
      • v-jiascu-msft's avatar
        v-jiascu-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        7 years ago

        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 )

        Cost-Per-Unit-Variances

         

         

        Best Regards,
        Dale