Forum Discussion
How to get the earlier value
- Anonymous3 years ago
Hi.
This is a general problem.
In SQL this could be solved using for example LAG function or WINDOW-functions.
Here is a solution in Power BI that works similarly:
Hope it works for you!
Pleace accept solution if it solves your problem, so it will be easier for others to find the answer.
0) Generate sample data
In Power BI modelling pane, create table:SampleData = DATATABLE( "ID", INTEGER, "Date Time", DATETIME, "Stage", STRING, { { 1411, "2020-09-22 00:00:00", "Stage 1" }, { 1411, "2020-09-23 00:00:00", "Stage 2" }, { 1411, "2020-09-24 00:00:00", "Won"}, { 1411, "2020-09-25 00:00:00", "Stage X" } } ), as shown below:
1a ) Create column with previous values of column Stage:
Previous Stage = CALCULATE ( LASTNONBLANK ( SampleData[Stage], TRUE() ), FILTER ( ALL ( SampleData ), SampleData[ID] = EARLIER ( SampleData[ID] ) && SampleData[Date Time] < EARLIER ( SampleData[Date Time] ) // && SampleData[Stage] <> "Won" ) ), as shown in picture below:
1a ) Create column with values before Stage="Won":Stage before Won = IF(SampleData[Stage]="Won", SampleData[Previous Stage], BLANK() )
All the steps above gives this result:Now, we just need to fill in the rows with the value before "Won" (e.g. Stage 2 in the column "Stage before Won")
2) Create a summary table using the column "Stage before Won"
This table contains 1 row pr ID with the value before Won:
Here is how you create the table:SummaryTable = SUMMARIZE( SampleData, SampleData[ID], "Calculated column", FIRSTNONBLANK(SampleData[Stage before Won],"") )The resulting table SummaryTable looks like this:
3 Join the summaryTable on ID to your data table (SampleData)
You can join in the values from the table SummaryTable into the SampleData in order the achive your desired !
Hope this fully or partially solves yor problem preetjawaria
Also, u can give feedback which of the steps above works well for you, and u can get additional tips on how to solve them smarter.
Kind regards
If so, please accept as solution so it easier for others to find.
Update: solution for step 3 in the above reply:
You can join the table as follows:
- connect SummarTable and SampleDate
- Add the calculated column as a measure in SampleDate as
Calculated column = related(SummaryTable[Calculated column])
As shown below:
This will produce your desired outcome 😀:
Did it work for you @Syndicate_Admin