Forum Discussion

yaya1974's avatar
yaya1974
Icon for Helper III rankHelper 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 look...
Need Help
Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper 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
    Icon for Helper III rankHelper 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!
    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      2 years ago

      That link takes me to a log-in page.

      • yaya1974's avatar
        yaya1974
        Icon for Helper III rankHelper III
        2 years ago

        Sorry.  I can send another pic.  But basically the the code below works for me except it is not filling in all the rows.  Do you know why or what I need to change to get the rows to fill in? 

         

        Thank you!