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 look...
Need Help
Ashish_Mathur's avatar
Ashish_Mathur
Super User
2 years ago

Hi,

I cannot understand the logic of the average formula which you have written in column C.  Please explain that clearly.

yaya1974's avatar
yaya1974
Helper III
2 years ago

Column C don't matter.  that code is working exactly how I need it to in powerbi.  I need a code to calculate the adjustment from column C with a 3% threshold.  the average is a look back period and the adjustment is the difference between the two periods.  I have the code working, it just isnt filling in all the rows with "same" number for the 6 months.  Either I need to take something out of my code or add something to get this to work.  That is  what I am asking.  To alter the code that I already that is working.  Thank you

  • yaya1974's avatar
    yaya1974
    Helper III
    2 years ago
    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_
    )