cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
darroniz New Member
New Member

COMPARE CURRENT MONTH PRICE VS CUMULATIVE LAST YEAR

Hi

 

I´m looking the way to compare my current month price vs last year cumulative final price, the calculation is something like this:

 

ITEMMONTHMONTH PRICEAVG PRICEDIF 
ITEM 1oct-1755  
ITEM 2oct-172020  
ITEM 3oct-178080  
ITEM 1nov-17107.5  
ITEM 2nov-173326.5  
ITEM 3nov-178582.5  
ITEM 1dic-1777.3  
ITEM 2dic-172526.0  
ITEM 3dic-178081.7  
ITEM 1ene-18111150% current ene-18 price vs YTD average price of 2017)
ITEM 2ene-182222-15% current ene-18 price vs YTD average price of 2017)
ITEM 3ene-18909010% current ene-18 price vs YTD average price of 2017)
ITEM 1feb-1847.52% current feb-18 price vs YTD average price of 2017)
ITEM 2feb-181518.5-29% current feb-18 price vs YTD average price of 2017)
ITEM 3feb-189190.511% 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 18feb-18
 DIF CURRENT MONTH VS AVERAGE PRICE OF ALL 2017DIF CURRENT MONTH VS AVERAGE PRICE OF ALL 2017
ITEM 150%2%
ITEM 2-15%-29%
ITEM 310%11%

 

 

Thank you

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: COMPARE CURRENT MONTH PRICE VS CUMULATIVE LAST YEAR

HI @darroniz ,

 

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
1 REPLY 1
Community Support Team
Community Support Team

Re: COMPARE CURRENT MONTH PRICE VS CUMULATIVE LAST YEAR

HI @darroniz ,

 

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |