Forum Discussion

ClaireBear's avatar
ClaireBear
Helper I
2 years ago
Solved

Convert Calculated Column into a Measure

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: Prev...
Need Help
  • ClaireBear's avatar
    ClaireBear
    2 years ago

    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's avatar
some_bih
Community Champion
2 years ago

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's avatar
ClaireBear
Helper I
2 years ago

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's avatar
    ClaireBear
    Helper I
    2 years ago

    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's avatar
    some_bih
    Community Champion
    2 years ago

    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]