Forum Discussion

tomekm's avatar
tomekm
Icon for Helper III rankHelper III
1 year ago
Solved

Value difference for max and min years

Hello,

 

I would need to determine the price difference for each ID, between their min year and max year, and then divide that amount by the number of year occurances for that specific ID. Here's how the data looks like. The last column is the desired output:

For ID=A, 33.45 - 36 = -2.55, divided by 3 = -0.85.

 

IDYear attributePricePrice Diff %: Min Year-Max Year/number of Years per ID
AY133.45-0.85
AY245.51-0.85
AY336-0.85
BY122.31.15
BY2201.15
CY155-5.5
CY252-5.5
CY377-5.5
CY465-5.5
CY532-5.5
CY688-5.5

 

 

Thank you.

 

Need Help
  • pankajnamekar25's avatar
    pankajnamekar25
    1 year ago

    Hello tomekm 

     

    Please try this Measure

     

    Price Diff =
    VAR MinPrice =
        CALCULATE(
            MINX(
                FILTER('Table', 'Table'[Year attribute] = MIN('Table'[Year attribute])),
                'Table'[Price]
            ),
            ALLEXCEPT('Table', 'Table'[ID])
        )
    VAR MaxPrice =
        CALCULATE(
            MAXX(
                FILTER('Table', 'Table'[Year attribute] = MAX('Table'[Year attribute])),
                'Table'[Price]
            ),
            ALLEXCEPT('Table', 'Table'[ID])
        )
    VAR YearCount = CALCULATE(COUNT('Table'[Year attribute]), ALLEXCEPT('Table', 'Table'[ID]))
    VAR PriceDiff = MinPrice - MaxPrice
    RETURN
    DIVIDE(PriceDiff, YearCount, 0)

    Thanks,
     Pankaj Namekar | LinkedIn

    If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

3 Replies

  • pankajnamekar25's avatar
    pankajnamekar25
    Icon for Super User rankSuper User
    1 year ago

    Hello tomekm 

     

    Please try this Measure

     

    Price Diff =
    VAR MinPrice =
        CALCULATE(
            MINX(
                FILTER('Table', 'Table'[Year attribute] = MIN('Table'[Year attribute])),
                'Table'[Price]
            ),
            ALLEXCEPT('Table', 'Table'[ID])
        )
    VAR MaxPrice =
        CALCULATE(
            MAXX(
                FILTER('Table', 'Table'[Year attribute] = MAX('Table'[Year attribute])),
                'Table'[Price]
            ),
            ALLEXCEPT('Table', 'Table'[ID])
        )
    VAR YearCount = CALCULATE(COUNT('Table'[Year attribute]), ALLEXCEPT('Table', 'Table'[ID]))
    VAR PriceDiff = MinPrice - MaxPrice
    RETURN
    DIVIDE(PriceDiff, YearCount, 0)

    Thanks,
     Pankaj Namekar | LinkedIn

    If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    1 year ago

    Hi,

    Here is a calculated column formula solution

    Column = divide(LOOKUPVALUE(Data[Price],Data[Year attribute],CALCULATE(min(Data[Year attribute]),FILTER(Data,Data[ID]=EARLIER(Data[ID]))),Data[ID],Data[ID])-LOOKUPVALUE(Data[Price],Data[Year attribute],CALCULATE(MAX(Data[Year attribute]),FILTER(Data,Data[ID]=EARLIER(Data[ID]))),Data[ID],Data[ID]),CALCULATE(COUNTROWS(Data),FILTER(Data,Data[ID]=EARLIER(Data[ID]))))

    Hope this helps.