creation of a calculated column to fill values ​​not present on a time series

[superME79]

Hello everybody,
I ask you for help to solve a small problem.
I have a table that does not always contain data in column M3
I would need to create a new column that fills in a new value for the column considering the following logic:
if M3 is filled add the next value of M1 and subtract the next value of M2
for the next row if M3 is empty take the first calculated value and add the next value of M1 and subtract the next value of M2.
I report the tables below
the first with the starting situation and the second with the desired
thank you for the support

[AilleryO]

Hi,

To get your result, my suggestion would be to add an index column to your data (button Index column in Power Query).

Once this done, it will be easier to make a cumulative calculation based on that index :

Measure =
VAR CurrentIndex = SELECTEDVALUE([Index])
VAR MyValue = IF( NOT( ISBLANK([M3])) , [M3] , XX)
RETURN

CALCULATE( YourCalculation , ALL(YourTable or Column) , CurrentIndex>=[Index] )

Not perfect but here is the idea, hope it helps

[superME79]

hank you for your contribution, unfortunately the table I am using is a table calculated in dax.
can you give me some indication to create an index column directly with a dax function?

[AilleryO]

By using RANKX you could add it, please refer to this post :

https://community.powerbi.com/t5/Desktop/Add-a-Calculated-index-column-using-DAX/m-p/310264#M137425

[superME79]

hello,
I managed to create the index but it is not clear to me how to use the construct.

can you explain better the code to use, I apologize but I'm still learning

[AilleryO]

Hi,

The idea behind my code is to cumulate all lines before the one you're on, I'm using an Index.

So if the calculation is made on line 13, before being made on line 14, 15...,

by saying VarIndex>=ColIndex, I'll take only lines above line 13.

Or if I want to calculate with the previous line I can say VarIndex=ColIndex-1...

So this colum is just used as a kind of bookmark to know where you are , so if you want the value of previous line you just do minus 1 ou +1 for the following...

It helps to move inside your records (lines).

Hope it makes more sense

[superME79]

Thank you for the support, I learned a new thing :), I was able to use code and I understood the logic but the result is far from what I needed,
now I am working with the indexes and the left join to move the present value to the next one remains the problem of understanding how to create a loop to make it do the analysis line by line I hope to be able to solve the problem thanks 😞

[AilleryO]

Hi,

What you call a loop is called iterator in DAX (OK some of you, might find this comparison a little easy).

Make a calculation line by line, is called iterating in DAX.

For instance, when you are using FILTER function, you are doing an iteration on all the lines of your table, likewise when you use all the function with an X ending their names (SUM vs SUMX, AVERAGE vs AVERAGEX...).

That's why in DAX Guide, you have the icon iterator (if you click on the link FILTER above).

So maybe in your case your could do a SUM of M3, in a variable.

VAR TotM3=SUM([M3])

This is assuming you have only one value in M3, is this right ?

Then do a SUMX (line by line) of M1-M2 (or else)

SUMX(Table, M1)-SUMX(Table, M2)+TotM3

Maybe you need a test to add the value of M3 only once, or choose to use the column since you have a value only on first line...

Do you dummy data to provide ?