Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi, I am stuck with achieving table level calculation, any quick help is appreciated. it could be a measure or column
Problem is to pick the appropriate cell value and subtract it against the previous cell value
I tried few dax but no luck
ID | EndWeek | Value | Index | Expected Results | Calculation | |||
11501 | 10/7/2018 | 242363 | 1 | 0 | Default "0" | If Index=1 then 0 | ||
11501 | 11/4/2018 | 248711 | 2 | 6348 | "=C3-C2" | 248711 - 242363 | Index1-Index2 | |
11501 | 12/2/2018 | 253163 | 3 | 4452 | "=C4-C3" | 253163 - 248711 | Index2-Index3 | |
11501 | 12/31/2018 | 258869 | 4 | 5706 | "=C5-C4" | |||
11501 | 1/27/2019 | 264559 | 5 | 5690 | "=C6-C5" | |||
11501 | 2/24/2019 | 270151 | 6 | 5592 | "=C7-C6" | |||
23488 | 3/24/2019 | 274269 | 1 | 0 | Default "0" | |||
23488 | 4/21/2019 | 279916 | 2 | 5647 | "=C9-C8" | |||
23488 | 5/19/2019 | 283859 | 3 | 3943 | "=C10-C9" | |||
23488 | 6/16/2019 | 290555 | 4 | 6696 | "=C11-C10" | |||
23488 | 7/14/2019 | 294742 | 5 | 4187 | "=C12-C11" |
Solved! Go to Solution.
Hey @praveenlc ,
I don't see a Fleet No in your example, but the following calculated column should work for the data you posted:
myResult =
VAR vCurrentValue = Facttable[Value]
VAR vCurrentIndex = Facttable[Index]
RETURN
IF(
vCurrentIndex = 1,
0,
vCurrentValue - CALCULATE(SUM(Facttable[Value]), Facttable[Index] = vCurrentIndex - 1 , ALLEXCEPT(Facttable, Facttable[ID]))
)
Hey @praveenlc ,
I don't see a Fleet No in your example, but the following calculated column should work for the data you posted:
myResult =
VAR vCurrentValue = Facttable[Value]
VAR vCurrentIndex = Facttable[Index]
RETURN
IF(
vCurrentIndex = 1,
0,
vCurrentValue - CALCULATE(SUM(Facttable[Value]), Facttable[Index] = vCurrentIndex - 1 , ALLEXCEPT(Facttable, Facttable[ID]))
)
hi @selimovd thanks and appreciate your quick response
I did try VAR on index but was not aware that VAR could be used for values also.
Thanks again!
Best wishes
Hey @praveenlc ,
I'm happy it works 😊
Yes, VAR is pretty dynamic, you can save the result of measures, the current column value or a row like done here and also whole tables, for example with the SUMMARIZE or VALUES function.
Best regards
Denis
ID | EndWeek | Value | Index | Expected Results | Calculation | ||||
11501 | 10/7/2018 | 242363 | 1 | 0 | Default "0" | If Index=1 then 0 | |||
11501 | 11/4/2018 | 248711 | 2 | 6348 | "=C3-C2" | 248711 - 242363 | Index1-Index2 | ||
11501 | 12/2/2018 | 253163 | 3 | 4452 | "=C4-C3" | 253163 - 248711 | Index2-Index3 | ||
11501 | 12/31/2018 | 258869 | 4 | 5706 | "=C5-C4" | ||||
11501 | 1/27/2019 | 264559 | 5 | 5690 | "=C6-C5" | ||||
11501 | 2/24/2019 | 270151 | 6 | 5592 | "=C7-C6" | ||||
23488 | 3/24/2019 | 274269 | 1 | 0 | Default "0" | ||||
23488 | 4/21/2019 | 279916 | 2 | 5647 | "=C9-C8" | ||||
23488 | 5/19/2019 | 283859 | 3 | 3943 | "=C10-C9" | ||||
23488 | 6/16/2019 | 290555 | 4 | 6696 | "=C11-C10" | ||||
23488 | 7/14/2019 | 294742 | 5 | 4187 | "=C12-C11" |
ID | EndWeek | Value | Index | Expected Results | Calculation | ||||||
11501 | 10/7/2018 | 242363 | 1 | 0 | Default "0" | If Index=1 then 0 | |||||
11501 | 11/4/2018 | 248711 | 2 | 6348 | "=C3-C2" | 248711 - 242363 | Index1-Index2 | ||||
11501 | 12/2/2018 | 253163 | 3 | 4452 | "=C4-C3" | 253163 - 248711 | Index2-Index3 | ||||
11501 | 12/31/2018 | 258869 | 4 | 5706 | "=C5-C4" | ||||||
11501 | 1/27/2019 | 264559 | 5 | 5690 | "=C6-C5" | ||||||
11501 | 2/24/2019 | 270151 | 6 | 5592 | "=C7-C6" | ||||||
23488 | 3/24/2019 | 274269 | 1 | 0 | Default "0" | ||||||
23488 | 4/21/2019 | 279916 | 2 | 5647 | "=C9-C8" | ||||||
23488 | 5/19/2019 | 283859 | 3 | 3943 | "=C10-C9" | ||||||
23488 | 6/16/2019 | 290555 | 4 | 6696 | "=C11-C10" | ||||||
23488 | 7/14/2019 | 294742 | 5 | 4187 | "=C12-C11" |