Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

YTD Change Calculated Column

Hi,

I have a table like below:

Current Balance  as_of_date  deal  Price  Value  
44412/14/22A703
567412/14/22A505
27912/14/22A608
..12/14/22B705
..12/14/22B603
..12/14/22B5010
..12/13/22A7020
..12/13/22A6090
..12/13/22A5040

 

I want to add a column YTD change such that for a particular deal, it is calculated as follows:-

 

YTD Change for Deal A for Price 70 = Filter the table for Deal A and Price 70 and then >> Value for Latest Date minus Value for 1st date of the year.

 

Similarly for Deal A for Price 50 = Filter the table for Deal A and Price 50 and then >> Value for Latest Date minus Value for 1st date of the year.

 

Need help with how to use DAX for this calculation

Need Help
  • Bifinity_75's avatar
    Bifinity_75
    3 years ago

    Hi Anonymous , try this:

     

    - Create this calculate column:

    Concat = Table_[deal  ]&Table_[Price  ]


    - Create this another calculate column:

    Result = var min_p=CALCULATE(MAX(Table_[Value  ]),ALLEXCEPT(Table_,Table_[Concat]))
return
min_p-Table_[Value  ]

    The result:

     

     

    Best Regards

     

     

     

     

1 Reply

  • Bifinity_75's avatar
    Bifinity_75
    Solution Sage
    3 years ago

    Hi Anonymous , try this:

     

    - Create this calculate column:

    Concat = Table_[deal  ]&Table_[Price  ]


    - Create this another calculate column:

    Result = var min_p=CALCULATE(MAX(Table_[Value  ]),ALLEXCEPT(Table_,Table_[Concat]))
return
min_p-Table_[Value  ]

    The result:

     

     

    Best Regards