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.
Hello, trying to create a calculated column using data from the previous row.
See example below for situation which has been calculated in excel for Indexation Factor.
Indexation factor formula = B2*(1+A3)
Within excel row B1 is manually set to 1 and the fields below are using the calculation above.
Can this be replicated using a calculated column within PowerBI? Thanks in advance.
(A) Selected Index | (B) Indexation Factor |
1 | |
0.029683 | 1.029683334 |
0.004578 | 1.03439749 |
0.049629 | 1.08573384 |
0.047982 | 1.137829244 |
0.030898 | 1.172985656 |
0.028848 | 1.206823699 |
0.019598 | 1.230475107 |
0.010779 | 1.24373867 |
0.021425 | 1.270385647 |
0.037423 | 1.31792673 |
0.030566 | 1.358210528 |
0.025874 | 1.393353256 |
0.012128 | 1.410252314 |
0.057762 | 1.491711364 |
0.123262 | 1.675582498 |
0.043063 | 1.747738101 |
Solved! Go to Solution.
Hi @mellon199
Here is a sample file with the solution https://we.tl/t-KyxjUgcvAi
For a caclulated column:
Indexation Factor =
VAR T1 =
FILTER (
Data,
Data[Index] <= EARLIER ( Data[Index] )
)
RETURN
PRODUCTX (
T1,
1 + Data[Selected Index]
)
For a measure:
Indexation Factor Measure =
VAR SelectedIndex =
SELECTEDVALUE ( Data[Index] )
VAR T1 =
CALCULATETABLE (
Data,
Data[Index] <= SelectedIndex,
ALL ( Data[Selected Index] )
)
RETURN
PRODUCTX (
T1,
1 + Data[Selected Index]
)
Hi @mellon199
Here is a sample file with the solution https://we.tl/t-KyxjUgcvAi
For a caclulated column:
Indexation Factor =
VAR T1 =
FILTER (
Data,
Data[Index] <= EARLIER ( Data[Index] )
)
RETURN
PRODUCTX (
T1,
1 + Data[Selected Index]
)
For a measure:
Indexation Factor Measure =
VAR SelectedIndex =
SELECTEDVALUE ( Data[Index] )
VAR T1 =
CALCULATETABLE (
Data,
Data[Index] <= SelectedIndex,
ALL ( Data[Selected Index] )
)
RETURN
PRODUCTX (
T1,
1 + Data[Selected Index]
)
Amazing @tamerj1 - Thank you for taking the time to complete a working example! 😊
@mellon199 See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])
VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
__Current - __Previous
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 |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
36 | |
33 | |
18 | |
18 |