if missing value exists get previous value

[QSense]

Hello,

I have a table that some year month's values are missing. On hand = previous on hand + input - output 

but if there is missing value on hand value is calculated wrongly. First table calculation is wrong.

Second table on hand calculation is true. 

since in 202006 previous on hand (30) input (1) - output (2) = 29

Please help.

ID	Y M	input	output	on hand
1	202002	5	8	30
1	202003	-	-	-
1	202004	-	-	-
1	202005	-	-	-
1	202006	1	2	-1
1	202007	10	9	0
1	202008	3	4	-1

ID	Y M	input	output	on hand
1	202002	5	8	30
1	202003	-	-	-
1	202004	-	-	-
1	202005	-	-	-
1	202006	1	2	29
1	202007	10	9	30
1	202008	3	4	29

[QSense]

1- I don't add missing months.

2- My purpose is whether there is 3 month or 12 month for each id , ı want to calculate on hand value based on previous on hand value.

In the below formula , when there is only one id , it works, but more than one ID it does not work

if (Row No() = 1 , [on hand],
if (
ID= previous(ID) and Row No() > 1,
peek([on hand],-1) + input- output,
If(
ID<> previous(ID) and Row No() = 1,[on hand],peek([on hand],-1) + input- output
)
)
)
as [on hand],

[QSense]

In below case, It does not work

[
ID, Y M, input, output, on hand
1, 202002,5, 8, 30
1, 202003,-,-,
1, 202004,-,-,
1, 202005,-,-,
1, 202006,1, 2, -1
1, 202007,10, 9, 0
1, 202008,3, 4, -1
2, 202004,10,5,25
2, 202005,3,7,-4
2, 202006,1, 3, -2
2, 202007,10, 9, 1
2, 202008,3, 4, -1
]

[lorenzoconforti]

Try this:

if (RowNo() = 1 , [on hand],
if (ID = previous(ID) and RowNo() > 1,
peek([on hand], -1) + input- output,
If(ID <> previous(ID),[on hand],peek([on hand],-1) + input- output
)
)
)
as [on hand],

[QSense]

as you see, it works for only one Id

[lorenzoconforti · Accepted solution]

It's working for me; see attached

you are clearly working on a different dataset as I can see date in between; maybe share your dashboard

[QSense]

Thank you . It also worked for me