calculate Monthly stock with missing values using future values

[omartineztr]

Hello comunity! I've been stucked on this for several weeks now!
I'm creating  a monthly report, the problem is that the logic of the ERP  that we are using (SAP) to create the datasource is not that "logical".
The system has  a database table to storage the month end values of the inventory stock, however, the History tables are updated with first movement after a period closure. If there is no movement for several months, then you will not see any history record for all those months that have past. SAP writes only a history record for the month preceding a change in stock or value.

All the periods between 2 records in a history table had the same stock/value all the time, hence you can take the value for those periods from the record following these missing periods.

For example, you could have the following information (I already have created a DataTable in order to show the missing months)

I need to calculate the values in a "backward" ways, considering the possibility that there could be more than 1 consecutive missing row.

Obviously, the report contains multiple material codes and multiple plants code.
Has anyone done something like this??

[bolfri]

Can you provide sample data to work with? 🙂 I can show you how to do this.

[omartineztr]

Thanks for the quick response? Do you mean additional to the picture?
I'm not sure if I can upload files, but here are some examples of how the data appears:

Plant	Material	Year	Month	Stock
G104	1000010691	2022	7	0
G104	1000010691	2022	9	0
G104	1000010691	2022	10	16
G104	1000010691	2022	12	20
G104	1000010691	2023	3	12

I need to be able to create a DAX that calculate the values and the table should look like this:

Plant	Material	Year	Month	Stock
G104	1000010691	2022	7	0
G104	1000010691	2022	8	0
G104	1000010691	2022	9	0
G104	1000010691	2022	10	16
G104	1000010691	2022	11	20
G104	1000010691	2022	12	20
G104	1000010691	2023	1	12
G104	1000010691	2023	2	12
G104	1000010691	2023	3	12

Thanks for your help!

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[omartineztr]

Awesome! It worked! Thank you very much you saved me!

[Ashish_Mathur]

You are welcome.