- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Appreciate your help!!
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Regards
Zubair
Please try my custom visuals
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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/
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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/
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 |
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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/
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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/
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks a Lot for you help guys, this is the solution!!!!!
🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @Rigoleto,
Please mark the right answers by clicking "Accept as Solution".
Best Regards,
Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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/
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
I would like as calculated column, please advise me about the logic , also include as measure if you can
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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/
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!!!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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/
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I still do n ot understand at all. Sorry.
Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Regards
Zubair
Please try my custom visuals
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
Subject | Author | Posted | |
---|---|---|---|
07-23-2024 11:45 PM | |||
05-05-2023 12:47 AM | |||
07-12-2023 11:25 PM | |||
10-29-2023 09:43 PM | |||
04-16-2023 05:34 AM |
User | Count |
---|---|
113 | |
80 | |
55 | |
54 | |
44 |
User | Count |
---|---|
176 | |
116 | |
77 | |
62 | |
54 |