DAX EARLIER/SUMX in PowerPivot for Excel returns different results compared to PowerBI

[ziomzbronxu]

I'm trying to use PowerPivot to calculate Cost basis with FIFO. The Calculated Column using DAX query returns bad results, while the same query in PowerBI calculates correctly. I suspect it's due to different (wrong?) processing of EARLIER statement. How could I fix the excel version?

The SUMX statement under VAR Previous_buys_balance returns the same results for each pass in PowerPivot, while in POWERBI behaves as expected - calculating cumulative sum over Previous_buys[Units]

[...]

ADDCOLUMNS(
            Previous_buys,
            "Cumulative",
            SUMX(
                FILTER(
                    Previous_buys,
                    [date] <= EARLIER([date])
                ),
                [Units]
            )[...]

PowerBI result

PowerPivot version - notice the same value for Cumulative (C: ) column in each pass (separated with |)

The whole calcuated column query

=
VAR myunits=[Units]
VAR Previous_buys =
    FILTER(
        Table1,   
        [Symbol] = EARLIER([Symbol])
            && [date] < EARLIER([date])
            && [type] = "buy"
    )
    
VAR Previous_sales =
    SUMX(
        FILTER(
            Table1,
            [Symbol] = EARLIER([Symbol])
                && [date] < EARLIER([date])
                &&[type] = "sale"
            ),
        [Units]
    )
    
VAR Previous_buys_balance =
    ADDCOLUMNS(
        ADDCOLUMNS(
            Previous_buys,
            "Cumulative",
            SUMX(
                FILTER(
                    Previous_buys,
                    [date] <= EARLIER([date])
                ),
                [Units]
            )
        ),
        "Balance Left",
        [Units] - IF(
            [Cumulative] < Previous_sales,
            [Units],
            VAR previouscumulative = [Cumulative]-[Units]
            RETURN IF (Previous_sales > previouscumulative, Previous_sales - previouscumulative)
        )
    )
    
VAR CostUsed =
    ADDCOLUMNS(
        ADDCOLUMNS(
            Previous_buys_Balance,
            "MyCumulatives",
            SUMX(
                FILTER(
                    Previous_buys_balance,
                    [date] < EARLIER([date])
                ),
                [Balance Left]
            )
        ),
        "Balance Used",
        IF(
            [Balance Left] <= myunits - [MyCumulatives],
            [Balance Left],
            IF ( myunits > [MyCumulatives], myunits -[MyCumulatives])
        )
    )

RETURN
IF ( [type] = "sale",
    [Units] * [value per unit] - SUMX( CostUsed, [Balance Used] * [value per unit])) 

Links to example files:

ExamplePBI.pbix

ExamplePP.xlsx

[daxer-almighty]

Hi @ziomzbronxu 

You should never use the EARLIER/EARLIEST function in your work. It's been deprecated (see https://dax.guide/earlier) and the recommended way to do it is to use variables (as @AlexisOlson does).

[AlexisOlson · Accepted solution]

This is odd. I think you're right that it has something to do with EARLIER functioning differently in Power Pivot because I can get Power Pivot to match Power BI if I eliminate all of the EARLIER uses by using variables instead.

Cost Basis =
VAR myunits = [Units]
VAR mysymbol = [Symbol]
VAR mydate = [date]
VAR Previous_buys =
    FILTER ( Table1, [Symbol] = mysymbol && [date] < mydate && [type] = "buy" )
VAR Previous_sales =
    SUMX (
        FILTER ( Table1, [Symbol] = mysymbol && [date] < mydate && [type] = "sale" ),
        [Units]
    )
VAR Previous_buys_balance =
    ADDCOLUMNS (
        ADDCOLUMNS (
            Previous_buys,
            "Cumulative",
                SUMX (
                    VAR rowdate = [date] RETURN FILTER ( Previous_buys, [date] <= rowdate ),
                    [Units]
                )
        ),
        "Balance Left",
            [Units]
                - IF (
                    [Cumulative] < Previous_sales,
                    [Units],
                    VAR previouscumulative = [Cumulative] - [Units]
                    RETURN
                        IF ( Previous_sales > previouscumulative, Previous_sales - previouscumulative )
                )
    )
VAR CostUsed =
    ADDCOLUMNS (
        ADDCOLUMNS (
            Previous_buys_Balance,
            "MyCumulatives",
                SUMX (
                    VAR rowdate = [date] RETURN FILTER ( Previous_buys_balance, [date] < rowdate ),
                    [Balance Left]
                )
        ),
        "Balance Used",
            IF (
                [Balance Left] <= myunits - [MyCumulatives],
                [Balance Left],
                IF ( myunits > [MyCumulatives], myunits - [MyCumulatives] )
            )
    )
RETURN
    IF (
        [type] = "sale",
        [Units] * [value per unit]
            - SUMX ( CostUsed, [Balance Used] * [value per unit] )
    )