Excel Calc in Power BI

Hello Rehansaeed2024 ,

Try this :

VAR Alpha = 0.964783
VAR CurrentIndex = [Row Index_Line_Items]

VAR MinIndex =
    CALCULATE (
        MIN ( 'Table A'[Row Index_Line_Items] ),
        ALLSELECTED ( 'Table A' )
    )

RETURN
SUMX (
    FILTER (
        ALLSELECTED ( 'Table A' ),
        'Table A'[Row Index_Line_Items] >= MinIndex
            && 'Table A'[Row Index_Line_Items] <= CurrentIndex
    ),
    'Table A'[Product Cost]
        * POWER (
            (1 - Alpha),
            CurrentIndex - 'Table A'[Row Index_Line_Items]
        )
)

Row Index_Line_Items must be unique

Hi Rehansaeed2024 ,

DAX MEASURE  Numerator Equivalent

Assuming:

[Row Index_Line_Items] = your sequential row index measure

'Table A'[Product Cost] = C column

Alpha = 0.964783

DAX measure.

Numerator =

VAR Alpha = 0.964783

VAR OneMinusAlpha = 1 - Alpha

VAR CurrentIndex =

    [Row Index_Line_Items]

VAR MinIndex =

    CALCULATE(

        MINX(ALLSELECTED('Table A'), [Row Index_Line_Items])

    )

RETURN

IF(

    ISBLANK(CurrentIndex) || ISBLANK(MinIndex),

    BLANK(),

    CALCULATE(

        SUMX(

            FILTER(

                ALLSELECTED('Table A'),

                [Row Index_Line_Items] <= CurrentIndex

            ),

            'Table A'[Product Cost] *

                POWER(

                    OneMinusAlpha,

                    CurrentIndex - [Row Index_Line_Items]

      )

        )

    )

)

If my response as resolved your issue please mark it as solution and give kudos.

Hi,

Share the download link of the MS Excel file with your formulas already written there so that i can clearly udnerstand your logic.

Hi Rehansaeed2024 ,

I've recreated your entire scenario in a new sample .pbix file to verify the behavior. After setting up the same tables, adding the Row Index, and applying the decay based numerator and denominator logic, I was able to reproduce the exact expected output on my side. Both the running numerator calculation and the denominator values are working correctly when the DAX measures are created under the LineItems table.

This confirms that the logic you are using is valid, and Power BI calculates the decayed SUMPRODUCT pattern exactly as intended once the Row Index and Alpha value are set up properly. For your reference, I've attached the working .pbix file so you can compare it with your model and follow the steps used to get the final result.

Hello Rehansaeed2024 

Try this



Numerator :=
VAR Alpha = 1 - 0.964783 -- If K4 = 0.964783, adjust as needed
VAR CurrentIndex = [Row Index_Line_Items]

RETURN
IF(
ISBLANK(CurrentIndex),
BLANK(),
CALCULATE(
SUMX(
ADDCOLUMNS(
'Table A',
"Weight",
POWER(
Alpha,
CurrentIndex - [Row Index_Line_Items]
),
"WeightedValue",
'Table A'[Product Cost] *
POWER(
Alpha,
CurrentIndex - [Row Index_Line_Items]
)
),
[WeightedValue]
),
FILTER(
ALLSELECTED('Table A'),
[Row Index_Line_Items] <= CurrentIndex
)
)
)