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:

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


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!

6 Replies

    • ClaireBear's avatar
      ClaireBear
      Helper I
      2 years ago

      Hello, thank you. 

       

      I did try this but it throws out an error:

       

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