Forum Discussion
Previous not blank value
Hi amitchandak ,
thanks a lot for your reply! Sadly it's not yet the solution I need. I will try to explain again:
The table [_PBI_Dispo_InventoryDev] now filtered on two [No_]. I added an Index for better explanation.
| Index | No_ | PostingDate | SumPerDay | GroupIndex | cumulativBestand | yDatePeriod |
| 1 | 1002 | 01.01.2015 | 23,65 | 1 | 23,65 | 2015 |
| 2 | 1002 | 12.03.2015 | -6,5 | 2 | 17,15 | 2015 |
| 3 | 1002 | 26.08.2015 | 86,4 | 3 | 103,55 | 2015 |
| 4 | 1002 | 16.09.2015 | -1 | 4 | 102,55 | 2015 |
| 5 | 1002 | 17.05.2016 | -0,35 | 5 | 102,2 | 2016 |
| 6 | 1002 | 18.05.2016 | -2,55 | 6 | 99,65 | 2016 |
| 7 | 1002 | 05.08.2016 | -3,1 | 7 | 96,55 | 2016 |
| 8 | 1002 | 08.08.2016 | -8,8 | 8 | 87,75 | 2016 |
| 9 | 1002 | 22.02.2018 | -6,6 | 9 | 81,15 | 2018 |
| 10 | 1003 | 01.01.2015 | 14,9 | 1 | 14,9 | 2015 |
| 11 | 1003 | 18.11.2015 | -5,9 | 2 | 9 | 2015 |
| 12 | 1003 | 02.12.2015 | -1,05 | 3 | 7,95 | 2015 |
| 13 | 1003 | 31.01.2018 | -0,95 | 4 | 7 | 2018 |
| 14 | 1003 | 01.02.2018 | -7 | 5 | 0 | 2018 |
Index 1 is the first entry (GroupIndex 1) for the No_ 1002 with the SumPerDay of 23,65 so the cumulativSum is 23,65.
Index 2 is the second entry (GroupIndex 2) for the No_ 1002 with the SumPerDay of -6,5 so the cumulativSum is 23,65 (from Index 1) - 6,5 = 17,5.
And so on...
The matrix table I try to achieve is the following (I also added a Index):
| Index | No_ | 2015 | 2016 | 2017 | 2018 | 2019 |
| 1 | 1001 | 14,90 | 14,90 | 14,90 | 7,00 | 7,00 |
| 2 | 1002 | 102,55 | 87,75 | 87,75 | 81,15 | 81,15 |
| 3 | 1003 | 200,69 | 172,25 | 105,45 | 199,70 | 168,30 |
I highlighted the values in the matrix table and in the table [_PBI_Dispo_InventoryDev] which refer to each other. As you can see the table [_PBI_Dispo_InventoryDev] does not have values for the year 2017 or 2019. Since this means that the inventory didn't change I want to show the last available value (for example for the Index 1 column 2017 it would be the value of the year 2015 since this would be the last available value).
The measure you wrote didn't work for me since you calculated the sum of the column SumPerDay and it didn't show values for the 'blank years' either.
I hope my explanation helped to make things clearer - can you help me with the measure?
Thanks!
I have used this on both rows and col of matrix
JanBauer , When I download the file, it does not download in the same format.
Hi amitchandak,
in what format do you need it?
I uploaded it as a zip file with two csv files and one pbix file in the zip when you extract it.
I can try sending it to you on another way - whatever suits you best 🙂
EDIT: I uploaded it to wetransfer - maybe this helps: https://we.tl/t-iPNs96DbQu