Reply
Rigoleto
Helper II
Helper II
Partially syndicated - Outbound

get value from Previous row

Need a funtion or logic to read a previous row, for instance:

 Fotos.png

 

 

 

 

 

 

 

 

 

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!!

 

 

 

1 ACCEPTED SOLUTION

Syndicated - Outbound

@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

gvc.png

View solution in original post

23 REPLIES 23
dil_d
Frequent Visitor

Syndicated - Outbound

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.

dil_d_1-1692533598147.png

 

 

Syndicated - Outbound

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? 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Syndicated - Outbound

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.

 

 

 

Syndicated - Outbound

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Syndicated - Outbound

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

Syndicated - Outbound

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Syndicated - Outbound

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
nakul555
Frequent Visitor

Syndicated - Outbound

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.

nakul555_0-1626667938840.jpeg

Is there any other way we could do this?

 

 

Rigoleto
Helper II
Helper II

Syndicated - Outbound

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

 

🙂

 

Syndicated - Outbound

Hi @Rigoleto,

 

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

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Ashish_Mathur
Super User
Super User

Syndicated - Outbound

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Syndicated - Outbound

Hi,

 

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

Syndicated - Outbound

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Syndicated - Outbound

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!!!

Capture.JPG

 

Syndicated - Outbound

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Syndicated - Outbound

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.

Syndicated - Outbound

I still do n ot understand at all.  Sorry.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Syndicated - Outbound

@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

gvc.png

Syndicated - Outbound

Great solution, thank you @Rigoleto!

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)