Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi
I´m looking the way to compare my current month price vs last year cumulative final price, the calculation is something like this:
ITEM | MONTH | MONTH PRICE | AVG PRICE | DIF | |
ITEM 1 | oct-17 | 5 | 5 | ||
ITEM 2 | oct-17 | 20 | 20 | ||
ITEM 3 | oct-17 | 80 | 80 | ||
ITEM 1 | nov-17 | 10 | 7.5 | ||
ITEM 2 | nov-17 | 33 | 26.5 | ||
ITEM 3 | nov-17 | 85 | 82.5 | ||
ITEM 1 | dic-17 | 7 | 7.3 | ||
ITEM 2 | dic-17 | 25 | 26.0 | ||
ITEM 3 | dic-17 | 80 | 81.7 | ||
ITEM 1 | ene-18 | 11 | 11 | 50% | current ene-18 price vs YTD average price of 2017) |
ITEM 2 | ene-18 | 22 | 22 | -15% | current ene-18 price vs YTD average price of 2017) |
ITEM 3 | ene-18 | 90 | 90 | 10% | current ene-18 price vs YTD average price of 2017) |
ITEM 1 | feb-18 | 4 | 7.5 | 2% | current feb-18 price vs YTD average price of 2017) |
ITEM 2 | feb-18 | 15 | 18.5 | -29% | current feb-18 price vs YTD average price of 2017) |
ITEM 3 | feb-18 | 91 | 90.5 | 11% | current feb-18 price vs YTD average price of 2017) |
At the end I want to show a matrix with the item lista and their difference
JAN 18 | feb-18 | |
DIF CURRENT MONTH VS AVERAGE PRICE OF ALL 2017 | DIF CURRENT MONTH VS AVERAGE PRICE OF ALL 2017 | |
ITEM 1 | 50% | 2% |
ITEM 2 | -15% | -29% |
ITEM 3 | 10% | 11% |
Thank you
Solved! Go to Solution.
HI @Anonymous ,
You can try to use following measure formula to compare current amount with previous YTD average:
Diff = VAR currDate = MAX ( Table[Month] ) VAR currPrice = CALCULATE ( SUM ( Table[Month Price] ), ALLSELECTED ( Table ), VALUES ( Table[Item] ), VALUES ( Table[Month] ) ) VAR PrevYTD = CALCULATE ( AVERAGE ( Table[Month Price] ), FILTER ( ALLSELECTED ( Table ), YEAR ( Table[Month] ) = YEAR ( currDate ) - 1 && Table[Month] <= DATE ( YEAR ( currDate ) - 1, MONTH ( currDate ), DAY ( currDate ) ) ), VALUES ( Table[Item] ) ) RETURN currPrice - prevYTD
Notice: I still not so sure for detail compare calculation, you can modify bold part with your own calculation formulas.
Regards,
Xiaoxin Sheng
HI @Anonymous ,
You can try to use following measure formula to compare current amount with previous YTD average:
Diff = VAR currDate = MAX ( Table[Month] ) VAR currPrice = CALCULATE ( SUM ( Table[Month Price] ), ALLSELECTED ( Table ), VALUES ( Table[Item] ), VALUES ( Table[Month] ) ) VAR PrevYTD = CALCULATE ( AVERAGE ( Table[Month Price] ), FILTER ( ALLSELECTED ( Table ), YEAR ( Table[Month] ) = YEAR ( currDate ) - 1 && Table[Month] <= DATE ( YEAR ( currDate ) - 1, MONTH ( currDate ), DAY ( currDate ) ) ), VALUES ( Table[Item] ) ) RETURN currPrice - prevYTD
Notice: I still not so sure for detail compare calculation, you can modify bold part with your own calculation formulas.
Regards,
Xiaoxin Sheng
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |