Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Solved! Go to 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] )
)
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).
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] )
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |