Forum Discussion

Rigoleto's avatar
Rigoleto
Helper II
8 years ago
Solved

get value from Previous row

Need a funtion or logic to read a previous row, for instance:                     How can you see Do I need is to sum up the previos record and build a new column with those values Ap...
Tips and Tricks
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Rigoleto

     

    I think this calculated column would work.

    See the attached sample file as well

     

    Calculated Column =
VAR PreviousRow =
    TOPN (
        1,
        FILTER (
            Table1,
            Table1[Month] < EARLIER ( Table1[Month] )
                && Table1[Employee] = EARLIER ( Table1[Employee] )
        ),
        [Month], DESC
    )
VAR PreviousValue =
    MINX ( PreviousRow, [Sales] )
RETURN
    Table1[Sales] + PreviousValue

Ashish_Mathur's avatar
Ashish_Mathur
Super User
3 years ago

Hi,

Share raw data in a format that can be pasted in an MS Excel file.  Is the FY from Aug - Jul?  If there is multiple years of data, then should the opening balance reset in August? 

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi Ashish,

We are calculating this for the current month onwards. If there are balance values we take it as an opening balance.
E.g:

MonthJuly(Opening Balance)AugSepOctNovDecJanFebMar
Budget Sales 17473641559823281870425475032909010223738025589052017217
Probable Monthly Receipts - AR8,613,088187899330635312738160.92763420.50000
Probable Monthly Receipts - Budget Sales 00001559823281870425475032909010
Exposure 8,481,459  6,977,751  7,058,294  8,842,377  10,191,564  9,610,240  9,621,642  8,729,849 

Here I want to get a measure for the Exposure row. That's the place I got confused about how to get a previous value and iterate the value over months.

 

 

 

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    Hi,

    That does not look like the raw dataset - that is the matrix view.  Share the raw dataset from where that matrix has to be built.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi Ashish,

       

      Hope this will work.

      Month-YearBudget ValueCurrent ARProbable Monthly Receipts - Budget SalesAR Value
      1-Jul-23 7,849,667  
      1-Aug-231747364 1,918,6971878993
      1-Sep-23155982301,825,6503063531
      1-Oct-23281870401,747,3642738161
      1-Nov-23254750301,559,8230
      1-Dec-23290901002,818,7040
      1-Jan-24223738002,547,5030
      1-Feb-24255890502,909,0100
      1-Mar-24201721702,237,3800
      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        3 years ago

        Hi,

        Share the download link of the working Excel file which you showed on Sunday.  I need to check something in the formulas there.