COMPARE CURRENT MONTH PRICE VS CUMULATIVE LAST YEAR

[Anonymous]

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

[v-shex-msft · Accepted 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