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 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??
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
Can you provide sample data to work with? 🙂 I can show you how to do this.
Proud to be a Super User!
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!
Hi,
You may download my PBI file from here.
Hope this helps.
Awesome! It worked! Thank you very much you saved me!
You are welcome.
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 |
---|---|
105 | |
105 | |
88 | |
73 | |
66 |
User | Count |
---|---|
124 | |
113 | |
98 | |
81 | |
72 |