Forum Discussion
Previous row value formula
Hi Team,
I read this post: https://community.powerbi.com/t5/Desktop/Calculated-column-From-To-formula/td-p/1274376 and it works for me too, but not for every row.
The given example is:
And the suggested formula is: From =
CALCULATE(SUM('Table'[Metric]),FILTER(ALLEXCEPT('Table','Table'[ID]),'Table'[Date]<EARLIER('Table'[Date])))+0
With this formula if the ID is the same, the “From’ column is zero for the first result and then it’s the same as “To” field. In my case I have two rows with the same ID, but on the same date (not different dates as in the example). If the ID is new, the From field should start again with 0. The result I’m looking for is:
Hi nasbkrv ,
Please add an Index column in Power Query Editor first. And then, create calculated columns like so:
From = CALCULATE ( SUM ( 'Table'[Metric] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[ID] ), 'Table'[Index] < EARLIER ( 'Table'[Index] ) ) ) + 0To = 'Table'[From] + 'Table'[Metric]Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
nasbkrv - Not 100% sure I understand but seems like you need an index column or something similar to identify previous rows. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586
Also, since you are a New Member, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.nasbkrv , In that you have to add an index column and try
https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi
Maxx(filter(table,[Index]=earlier([Index])-1),[Metric])
Hi nasbkrv ,
Please add an Index column in Power Query Editor first. And then, create calculated columns like so:
From = CALCULATE ( SUM ( 'Table'[Metric] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[ID] ), 'Table'[Index] < EARLIER ( 'Table'[Index] ) ) ) + 0To = 'Table'[From] + 'Table'[Metric]Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Icey ,
I'm testing the suggested formula but with a larger file and the system gives me an error that the file is too big and there is no enough memory. Any ideas if there is another formula or function except earlier which gives the same result?
Thank you.
