Convert Calculated Column into a Measure

[ClaireBear]

Hi, 

Is there a way to convert the following calculated column into a measure? Due to the size of the report, a calculated column is throwing out a memory error. 

Calculated column:

Previous Product Price 3 =

VAR PreviousRow =

    TOPN (

        1,

        FILTER (

            Fact_PO,

            Fact_PO[Doc. Date] < EARLIER ( Fact_PO[Doc. Date] )

                && Fact_PO[Material] = EARLIER ( Fact_PO[Material] )

        ),

        Fact_PO[Doc. Date], DESC

    )

VAR PreviousValue =

    MINX ( PreviousRow, [Net Price ZAR] )

RETURN

PreviousValue

I am trying to work out the previous row value by date to determin if there has been a change in price or not. 
I then need to calculate the product % change.
The calculated column works on test data (a few rows of data) but when applied to live data, it throws out a memory error, hence the request to change it into a measure.  (the issue looks like the "earlier" function)

Thank you

[some_bih]

Hi @ClaireBear not enought infos what is grain of data you have in model and expected level of output. Still, try Measure test

PreviousValue Measure test =
VAR PreviousRow =
TOPN (
1,
FILTER (
Fact_PO,
Fact_PO[Doc. Date] < MAX ( Fact_PO[Doc. Date] )
&& Fact_PO[Material] = SELECTEDVALUE ( Fact_PO[Material] )
),
Fact_PO[Doc. Date], DESC
)
RETURN
MINX ( PreviousRow, [Net Price ZAR] )

[ClaireBear]

Hi

Thank you again, The measure unfortunaly returns a blank column. 

The table below shows the example data, and the format/structure required.
- There are 2 products with a "Net Price Zar" column by doc date.
- I have added a calculated Column (Calculated Column_Previous Product Price) which shows me the exact result i would like as a measure. 
- I want to use a "Measure" instead of a calculated column and get the same result as the "calculated column_previous product" below, same grain of data. 
- Even though the calculated column results are correct, it is throwing out a memory error with the live data which is over 100 000 rows so the calculated column is not suitable. 
- Most examples available illustrate an index or a date with the previous value calculation which is great, but my issue is that i need the date and the previous value by product in the calculation. 

So i would like the measure to show the same results as the calculated column in the example below. 

I hope this makes more sense, i appreaciate any advice. 

[ClaireBear · Accepted solution]

Hi

I think i found a solution

I changed the calcuation to read the following:

Measure_Previous Product Price =

VAR currDate =

    MAX ( Fact_PO[Doc. Date])

VAR currSKU =

    SELECTEDVALUE (Fact_PO[Material] )

VAR currSupplier =

    SELECTEDVALUE (Fact_PO[Name of Vendor] )

VAR prevDate =

    CALCULATE (

        MAX ( Fact_PO[Doc. Date] ),

        FILTER (

            ALLSELECTED ( Fact_PO ),

            [Doc. Date] < currDate

                && [Name of Vendor] = currSupplier

                && [Material] = currSKU

        )

    )

RETURN

    CALCULATE (

        MIN (Fact_PO[Net Price ZAR] ),

        FILTER (

            ALLSELECTED (Fact_PO ),

            [Doc. Date] = prevDate

                && [Name of Vendor] = currSupplier

                && [Material] = currSKU

        )

    )

I now get the same result:

Thank you!

[some_bih]

Hi @ClaireBear 

So you want "just" previous row value? Your TOPN misslead me 🙂

The previous row value is based on two columns:Fact_PO[Doc. Date] and  Fact_PO[Material]

[some_bih]

Hi @ClaireBear simple create new measure with your definition.

[ClaireBear]

Hello, thank you. 

I did try this but it throws out an error: