get value from Previous row

[Rigoleto]

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

Appreciate your help!!

[dil_d]

Hi Please help me with this matter,

I need to sum up the current value with the previous month's value. Please help me to get this done in power bi. Appreciate your help.

[Ashish_Mathur]

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? 

[dil_d]

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:

Month	July(Opening Balance)	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar
Budget Sales		1747364	1559823	2818704	2547503	2909010	2237380	2558905	2017217
Probable Monthly Receipts - AR	8,613,088	1878993	3063531	2738160.92	763420.5	0	0	0	0
Probable Monthly Receipts - Budget Sales		0	0	0	0	1559823	2818704	2547503	2909010
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]

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.

[dil_d]

Hi Ashish,

Hope this will work.

Month-Year	Budget Value	Current AR	Probable Monthly Receipts - Budget Sales	AR Value
1-Jul-23		7,849,667
1-Aug-23	1747364		1,918,697	1878993
1-Sep-23	1559823	0	1,825,650	3063531
1-Oct-23	2818704	0	1,747,364	2738161
1-Nov-23	2547503	0	1,559,823	0
1-Dec-23	2909010	0	2,818,704	0
1-Jan-24	2237380	0	2,547,503	0
1-Feb-24	2558905	0	2,909,010	0
1-Mar-24	2017217	0	2,237,380	0

[Ashish_Mathur]

Hi,

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

[dil_d]

Hi,

https://docs.google.com/spreadsheets/d/19jgEY3BpOPOnlJEAiV4O4YHRnVU2gnyF/edit?usp=sharing&ouid=11377...

[Ashish_Mathur]

I really do not understand.  How have you got the figure of 8613088 in the visual - in the Table, the figue for July is 7849667.  What is the logic of picking up the budgeted sales figures?  Is it that when the AR value is 0, then tak the budgeted sales figure?  Please give a proper explanation rather than leaving thing to mere guess work.

[nakul555]

Hello @Zubair_Muhammad,

I was trying to do something similar but I have a large dataset (~2M rows) and PBI desktop is unable to process it. It gives me this error. I think it has something to do with 2 earlier statements.

Is there any other way we could do this?

[Rigoleto]

Thanks a Lot for you help guys, this is the solution!!!!!

🙂

[v-jiascu-msft]

Hi @Rigoleto,

Please mark the right answers by clicking "Accept as Solution".

Best Regards,

Dale

[Ashish_Mathur]

Hi,

Do youw want that as a measure or as a calculated column formula?

[Rigoleto]

Hi,

I would like as calculated column, please advise me about the logic , also include as measure if you can

[Ashish_Mathur]

Hi,

In what order should the numbers appear in column B?  Should they be in descending order?

[Rigoleto]

Basically Do I need to do or get from the logic is something like this

Notice in the screeshot that I have 3 employees, every single employee has sales by month and the sum of sales is doing for every employee, no mixed up with a previous employee, that is the logic that I need in order to calculate the Sumed column

Thanks in advance for your help!!!

[Ashish_Mathur]

Hi,

Please share a meaningful dataset.  While your column title is month, entries under that column are Sales 1, Sales 2.  I am sure there are months/dates in that column.  Please share a meaningful dataset with column names and data therein making sense so that i can frame a solution.

[dil_d]

Hi,

I am really sorry for the inconvenience that occurred. Just forget about the first dataset I shared. In my previous email, I shared a Google sheet link with the dataset. Hope that would be helpful.

[Ashish_Mathur]

I still do n ot understand at all.  Sorry.

[Zubair_Muhammad · Accepted solution]

@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

[Csaba]

Great solution, thank you @Rigoleto!