Previous not blank value

[JanBauer]

Hi all,

I have the following matrix:

No_	2015	2016	2017	2018	2019
1001	14,90	0,00	0,00	7,00	0,00
1002	103,55	102,20	0,00	81,15	0,00
1003	200,69	172,25	105,45	199,70	168,30

The measure that caluculates the values looks like this:

m_maxBestand = IF(ISBLANK(MAX(_PBI_Dispo_InventoryDev[cumulativBestand]));0;MAX(_PBI_Dispo_InventoryDev[cumulativBestand]))

The table [_PBI_Dispo_InventoryDev] looks as follows (filtered on '1002' for a better overview):

No_	PostingDate	SumPerDay	GroupIndex	cumulativBestand	yDatePeriod
1002	01.01.2015	23,65	1	23,65	2015
1002	12.03.2015	-6,5	2	17,15	2015
1002	26.08.2015	86,4	3	103,55	2015
1002	16.09.2015	-1	4	102,55	2015
1002	17.05.2016	-0,35	5	102,2	2016
1002	18.05.2016	-2,55	6	99,65	2016
1002	05.08.2016	-3,1	7	96,55	2016
1002	08.08.2016	-8,8	8	87,75	2016
1002	22.02.2018	-6,6	9	81,15	2018

What I want to achieve is that my measures shows the latest available value instead of 0 so that the matrix looks like the following:

No_	2015	2016	2017	2018	2019
1001	14,90	14,90	14,90	7,00	7,00
1002	103,55	102,20	102,20	81,15	81,15
1003	200,69	172,25	105,45	199,70	168,30

Is this possible and if so - how?

Thanks!

[v-lionel-msft]

Hi @JanBauer ,

Is this what you want?

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

Try like

Cumm  = CALCULATE(SUM(Table[SumPerDay]),filter(table,table[No_] <=maxx(table,date[No_]) && table[yDatePeriod] =max(table[yDatePeriod]) ))

[JanBauer]

Hi @amitchandak,

can you explain the DAX formular?

I'm not quite sure if you understood my enquiry correctly.

Thanks.

[v-lionel-msft]

Hi @JanBauer ,

Please refer to @amitchandak 's answer, if it can help you, please Accept it as the solution to help the other members find it more quickly, if not, please show the data that [No_] is equal to 1001

Best regards,
Lionel Chen

[amitchandak]

@JanBauer ,

Please find solution at https://www.dropbox.com/s/q5hxd2ztdca48nq/Previous%20not%20blank%20value.pbix?dl=0

[JanBauer]

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!

[amitchandak]

I have used this on both rows and col of matrix

[JanBauer]

Still does't work for me.

Can you look at my example file, @amitchandak - maybe this helps:

Example file 

[amitchandak]

@JanBauer , When I download the file, it does not download in the same format.

[JanBauer]

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