Formula Help

[yaya1974]

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

 

[Ashish_Mathur]

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]

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]

That link takes me to a log-in page.

[yaya1974]

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!

[Ashish_Mathur]

Hi,

I will need a file to work with.  So share the download link of the file with your Excel formulas intact.

[yaya1974]

see if this link works.

https://www.dropbox.com/scl/fi/hwn6ww6n1r79xx68wnjtw/example.xlsx?rlkey=hyockbc6r5m5k3ch8rbg6626v&dl...

Thank you

[Ashish_Mathur]

Hi,

1. I wonder what is the use of the IF() function in column D when the result is the same in the value if true and value if false placeholders i.e. $C$56-$C$55
2. What should the trigger be for changing the range reference of the average function in column D i.e. when the number in column C changes
3. Why does the first set of coloured cells in column D have 7 rows and the other sets have only 6 rows?

[Ashish_Mathur]

Hi,

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

[yaya1974]

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]

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_

)

[yaya1974]

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!!