Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi All,
I'm trying to do this on Power BI, but I can't figure out the formula to do this. I want to use the previous calculated value from Column E to the next row and so on. Is it possible to be done on Power BI?
Below is the formula on Excel:
The data value:
Thank you!
Solved! Go to Solution.
Hi @BCAdmin ,
I have a test on amitchandak's code and I find that I won't return the result you want. Firstly, we need to add an Index column in Power Query if you only have month column in text format.
Your table will be like as below.
Then add calculated columns in this table.
Column4 =
VAR _START =
CALCULATE ( SUM ( 'Table'[Column1] ), ALL ( 'Table' ) )
RETURN
_START
+ SUMX (
FILTER ( 'Table', 'Table'[Index] <= EARLIER ( 'Table'[Index] ) ),
[Column2] - [Column3]
)
New Column1 =
VAR _PREVIOUS_COLUMN4 =
CALCULATE (
SUM ( 'Table'[Column4] ),
FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) - 1 )
)
RETURN
IF ( ISBLANK ( 'Table'[Column1] ), _PREVIOUS_COLUMN4, 'Table'[Column1] )
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @BCAdmin ,
I have a test on amitchandak's code and I find that I won't return the result you want. Firstly, we need to add an Index column in Power Query if you only have month column in text format.
Your table will be like as below.
Then add calculated columns in this table.
Column4 =
VAR _START =
CALCULATE ( SUM ( 'Table'[Column1] ), ALL ( 'Table' ) )
RETURN
_START
+ SUMX (
FILTER ( 'Table', 'Table'[Index] <= EARLIER ( 'Table'[Index] ) ),
[Column2] - [Column3]
)
New Column1 =
VAR _PREVIOUS_COLUMN4 =
CALCULATE (
SUM ( 'Table'[Column4] ),
FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) - 1 )
)
RETURN
IF ( ISBLANK ( 'Table'[Column1] ), _PREVIOUS_COLUMN4, 'Table'[Column1] )
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@BCAdmin , Assume you have date or index column (add in power query)
You can create a new column
[Column1] - Sumx(filter(Table, [Date] <= earlier([date]) ), [Column2] -[Column3])
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
24 | |
21 | |
17 | |
14 | |
10 |
User | Count |
---|---|
42 | |
35 | |
25 | |
23 | |
23 |