March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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.
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
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.
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?
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.
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.
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 |
Hi,
Share the download link of the working Excel file which you showed on Sunday. I need to check something in the formulas there.
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.
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?
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
Hi,
Do youw want that as a measure or as a calculated column formula?
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?
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!!!
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.
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.
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
87 | |
86 | |
75 | |
49 |
User | Count |
---|---|
164 | |
149 | |
101 | |
73 | |
55 |