Forum Discussion

yaya1974's avatar
yaya1974
Helper III
2 years ago

Formula Help

I am trying to subtract same column but different row, or previous period.  Everything I have tried keeps giving me the same value as in original column.   Here is screen shot of the output I am looking for in the adjument column.

 

Grateful for any help!

Thank you

Version: 2.131.901.0 64-bit

 

Need Help

11 Replies

  • yaya1974's avatar
    yaya1974
    Helper III
    2 years ago

    Hi I got it to work with this formula, but I need it to fill in all the rows.  Can someone help with that?

    Plus I am still working on the 3% threshold, if anyone can help to add that in as well  🙂

    Thank you!!

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    Hi,

    Share the download link of the Excel file with your formulas already written there.  I will try to convert them into DAX formulas/measures. 

    • yaya1974's avatar
      yaya1974
      Helper III
      2 years ago

      I don't have in excel the screen shot below is of powerbi.  I can copy the code in:

       

      Navistar Steel Adj =
      VAR PreviousDate_ =
          CALCULATE(
              MAX(Commodities[Date1]),
              Commodities[Date1] < EARLIER(Commodities[Date1]),
              ALLEXCEPT(Commodities,Commodities[Navistar Steel CRU Index Cost/lb])
          )
          VAR PreviousValue_ =
              CALCULATE(
                  DISTINCT(Commodities[Navistar Steel CRU Index Cost/lb]),
                  Commodities[Date1] = PreviousDate_,
                  ALLEXCEPT(Commodities,Commodities[Navistar Steel CRU Index Cost/lb])
              )
          VAR CurrentValue_ = Commodities[Navistar Steel CRU Index Cost/lb]
          RETURN
              IF(
                  NOT ISBLANK(CurrentValue_) && NOT   ISBLANK(PreviousValue_),
                  CurrentValue_ - PreviousValue_
      )
       
      The adjustment column is the only one I need help with.  I got the formula to work but I need it to fill in all rows not leave zeros., but also add a 3% threshold to the formula.  
       
      example.xlsx  - not sure if link will work or not.
       
      Thank you!