Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Rigoleto
Helper II
Helper II

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

@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


Regards
Zubair

Please try my custom visuals

View solution in original post

23 REPLIES 23
dil_d
Frequent Visitor

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

 

 

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/

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.

 

 

 

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/

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

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/

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

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

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

 

🙂

 

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

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/

Hi,

 

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

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/

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

 

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/

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.

I still do n ot understand at all.  Sorry.


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

@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


Regards
Zubair

Please try my custom visuals

Great solution, thank you @Rigoleto!

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.