Forum Discussion

JanBauer's avatar
JanBauer
Frequent Visitor
6 years ago

Previous not blank value

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,7...
Need Help
amitchandak's avatar
amitchandak
Super User
6 years ago

Try like

Cumm  = CALCULATE(SUM(Table[SumPerDay]),filter(table,table[No_] <=maxx(table,date[No_]) && table[yDatePeriod] =max(table[yDatePeriod]) ))
JanBauer's avatar
JanBauer
Frequent Visitor
6 years ago

Hi amitchandak,

 

can you explain the DAX formular?

 

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

 

Thanks.

 

    • JanBauer's avatar
      JanBauer
      Frequent Visitor
      6 years ago

      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.

       

      IndexNo_PostingDateSumPerDayGroupIndexcumulativBestandyDatePeriod
      1100201.01.201523,65123,652015
      2100212.03.2015-6,5217,152015
      3100226.08.201586,43103,552015
      4100216.09.2015-14102,552015
      5100217.05.2016-0,355102,22016
      6100218.05.2016-2,55699,652016
      7100205.08.2016-3,1796,552016
      8100208.08.2016-8,8887,752016
      9100222.02.2018-6,6981,15

      2018

      10100301.01.201514,9114,9

      2015

      11100318.11.2015-5,929

      2015

      12100302.12.2015-1,0537,95

      2015

      13100331.01.2018-0,9547

      2018

      14100301.02.2018-750

      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):

       

      IndexNo_20152016201720182019
      1100114,9014,9014,907,007,00
      21002102,5587,7587,7581,1581,15
      31003200,69172,25105,45199,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's avatar
        amitchandak
        Super User
        6 years ago

        I have used this on both rows and col of matrix

         

  • v-lionel-msft's avatar
    v-lionel-msft
    Community Support
    6 years ago

    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