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.
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?
Solved! Go to 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
Hi @Anonymous,
Can you share a sample of the original data, please?
Best Regards,
Dale
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 |
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
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 |
---|---|
108 | |
98 | |
81 | |
65 | |
62 |
User | Count |
---|---|
147 | |
116 | |
104 | |
88 | |
65 |