Create a new column to calculate the Current Price

[AlvaroPouso]

I have a single table ("AjusteDePrecios")with the columns "IDCustomer", "IDProduct", "OriginalPrice", "Validity From" and "Coefficient". I need to create a new column called "Current Price" and that is calculated for each "Effective From" date by multiplying the "Current Price" of the immediately preceding date by the coefficient of the effective date from the line that is being calculated. The "current price" of the first "effective from" date is equal to the "original price" and the "coefficient" is equal to 1.

I tried to do it with the following solution, but it doesn't work.

CurrentPrice =

VAR PrecioAnterior =

    CALCULATE(

        [CurrentPrice],

        FILTER(

            ALL(AjusteDePrecios),  

            AjusteDePrecios[IDCustomer] = AjusteDePrecios[IDCustomer] &&

            AjusteDePrecios[IDProduct] = AjusteDePrecios[IDProduct] &&

            AjusteDePrecios[Validity From] < EARLIER(AjusteDePrecios[Validity From]) 

        )

    )

VAR Resultado =

    IF(

        ISBLANK(PrecioAnterior),

        AjusteDePrecios[OriginalPrice], 

        PrecioAnterior * AjusteDePrecios[Coefficient] 

    )

RETURN

    Resultado

You could help me by finding out what the error is and the possible solution.

Actually, it seems simple, but since I'm just starting to work with Power BI, I'm not sure about using variables.

[Anonymous]

Hi @AlvaroPouso 
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[Anonymous]

Hi @AlvaroPouso 
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[Anonymous]

Hi @AlvaroPouso 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[Anonymous · Accepted solution]

Hi @AlvaroPouso 
Thank you for reaching out microsoft fabric community forum.

Thanks for confirming the column names

Since DAX can't handle row-by-row recursive calculations in calculated columns, the best approach here is to use Power Query. Below is a ready-to-use M script that calculates PriceCurrent by grouping by IDCustomer and IDProduct, sorting by Date From, and applying your logic:

let

    Source = PriceAdjustment,

    SortedRows = Table.Sort(Source, {

        {"IDCustomer", Order.Ascending},

        {"IDProduct", Order.Ascending},

        {"Date From", Order.Ascending}

    }),

    Grouped = Table.Group(SortedRows, {"IDCustomer", "IDProduct"}, {

        {"AllRows", (tbl) =>

            let

                Sorted = Table.Sort(tbl, {{"Date From", Order.Ascending}}),

                AddIndex = Table.AddIndexColumn(Sorted, "Index", 0, 1, Int64.Type),

                PriceList = List.Generate(

                    () => [i = 0, p = Record.Field(AddIndex{0}, "PriceBase")],

                    each [i] < Table.RowCount(AddIndex),

                    each [

                        i = [i] + 1,

                        p = [p] * Record.Field(AddIndex{[i]+1}, "Coefficient")

                    ],

                    each [p]

                ),

                AddPriceCurrent = Table.AddColumn(AddIndex, "PriceCurrent", (r) => PriceList{r[Index]}),

                Cleaned = Table.RemoveColumns(AddPriceCurrent, {"Index"})

            in

                Cleaned

        , type table}

    }),

    Final = Table.Combine(Grouped[AllRows])

in
   Final

• This Starts PriceCurrent with PriceBase
• Multiplies by Coefficient for each following row in the same customer-product group
•   Assumes data is ordered by Date From
  
  If this solution helps, please consider giving us Kudos and accepting it as the solution so that it may assist other members in the community
  Thank you.

[burakkaragoz]

Hi @AlvaroPouso ,

Thanks for sharing the details. You're on the right track, but the issue here is that calculated columns in Power BI can’t reference themselves recursively — so trying to use [CurrentPrice] inside its own definition won’t work.

To achieve this kind of logic (where each row depends on the previous one), you’ll need to use Power Query instead of DAX, or switch to a calculated measure if your use case allows it (though measures don’t persist row-level values).

If you want to stick with DAX and calculated columns, here’s a workaround idea:

1. Sort your table by IDCustomer, IDProduct, and Validity From.
2. Use a running calculation in Power Query to simulate the price evolution row by row.

But if you really want to try it in DAX, you’ll need to pre-calculate the previous price using a calculated column that looks up the most recent previous row. Here's a simplified version of how you might approach it:

PreviousPrice = 
VAR PrevDate =
    CALCULATE(
        MAX(AjusteDePrecios[Validity From]),
        FILTER(
            AjusteDePrecios,
            AjusteDePrecios[IDCustomer] = EARLIER(AjusteDePrecios[IDCustomer]) &&
            AjusteDePrecios[IDProduct] = EARLIER(AjusteDePrecios[IDProduct]) &&
            AjusteDePrecios[Validity From] < EARLIER(AjusteDePrecios[Validity From])
        )
    )
RETURN
    LOOKUPVALUE(
        AjusteDePrecios[CurrentPrice],
        AjusteDePrecios[IDCustomer], AjusteDePrecios[IDCustomer],
        AjusteDePrecios[IDProduct], AjusteDePrecios[IDProduct],
        AjusteDePrecios[Validity From], PrevDate
    )

But again, this will only work if [CurrentPrice] is already calculated, which brings us back to the recursion issue.

Best solution: Do this logic in Power Query using the Index column and a custom step to multiply row by row.

Let me know if you want help building that step in Power Query.

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.

[Syndicate_Admin]

Hello, thank you very much for your answer.

I understand then that the solution would be to do it in Power Query.

Please can you help me since I don't have a very deep knowledge of the M language either, and I'm not sure how to make conditionals to work with immediately previous dates, etc.

[burakkaragoz]

Hi @Syndicate_Admin ,

Sure, I can help you build this logic in Power Query. Here's a step-by-step approach to calculate the "Current Price" based on the "Original Price" and "Coefficient", using the "Validity From" date to determine the order.

Steps in Power Query:

1. Sort the table
   Sort your table by IDCustomer, IDProduct, and Validity From in ascending order.

2. Add an Index column
   This will help us track the order of price changes per customer-product combination.

3. Group the data
   Group by IDCustomer and IDProduct. Inside each group:

   • Sort again by Validity From
   • Add an index starting from 0
   • Add a custom column to calculate the "Current Price"

4. Use a custom column with a loop
   In the grouped table, use a custom function to iterate through the rows and apply the logic:

   • First row: Current Price = Original Price
   • Next rows: Current Price = Previous Current Price * Coefficient

Since Power Query doesn’t support recursion directly, we simulate it using a List.Generate or a custom function inside each group.

If you're not familiar with M code, I can help you write the exact script. Just let me know the column names and if you're okay with grouping logic.
translation and formatting supported by AI

[Syndicate_Admin]

Hello, if I agree with the grouping logic and would need help with the M language.

The table or query to which you should add the new column is "PriceAdjustment" and the names of the columns are "CustomerID"; IDProducto"; "Date From";" BasePrice";" Coefficient".

Please thank you for helping me with the script.

[AlvaroPouso]

Yes, I would need help with the M language script. The names of the columns would be (revised names.

IDCustomer

IDProduct

PriceBase

Coefficient

The new column would be: PriceCurrent

I would really appreciate your help.

[Ashish_Excel]

Hi,

Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.