Forum Discussion
Track the progression
Hi Team,
How can we track the progression of an account?
For example if we have a list of account with their status changes date and the status and I want to see for a account what was it's previoud status and date also what is the current status and the change date.
Thanks!
Hi vijayKaushal ,
You can track account progression using an Index column per account and a DAX measure to fetch previous status and date.
Steps to follow :
In Power Query, group your data by Account and add an Index column to track the order of changes.
Then, use a DAX measure like this to pull the previous status:
Previous Status =
VAR i = 'Table'[Index]
RETURN CALCULATE(
MAX('Table'[Status]),
FILTER('Table', 'Table'[Account] = EARLIER('Table'[Account]) && 'Table'[Index] = i - 1)
)
You can do the same for the previous status date.
This way, for every current status, you’ll also see what the status was just before — super useful for progression tracking!
If you'd prefer to do this without any DAX, you can handle it fully in Power Query:
- Sort your data by Account and Status Date (ascending).
- Group by Account:
In Power Query: Home → Group By
Group by Account, and select “All Rows”.
Inside each group:
Add an Index column (starts at 1).
Add a custom column that references the previous row using the Index.
Here is an example to get previous status:
= if [Index] = 1 then null
else #"Previous Step"{[Index]-2}[Status]- Same logic for previous date: just replace [Status] with [Status Date].
- Expand the grouped tables back into the full table.
This gives you a clean table showing:
Current Status
Current Status Date
Previous Status
Previous Status Date
No DAX, all done in Power Query!
⭐Hope this solution helps you make the most of Power BI! If it did, click 'Mark as Solution' to help others find the right answers.
💡Found it helpful? Show some love with kudos 👍 as your support keeps our community thriving!
🚀Let’s keep building smarter, data-driven solutions together! 🚀 [Explore More]
6 Replies
Hi vijayKaushal ,
You can track account progression using an Index column per account and a DAX measure to fetch previous status and date.
Steps to follow :
In Power Query, group your data by Account and add an Index column to track the order of changes.
Then, use a DAX measure like this to pull the previous status:
Previous Status =
VAR i = 'Table'[Index]
RETURN CALCULATE(
MAX('Table'[Status]),
FILTER('Table', 'Table'[Account] = EARLIER('Table'[Account]) && 'Table'[Index] = i - 1)
)
You can do the same for the previous status date.
This way, for every current status, you’ll also see what the status was just before — super useful for progression tracking!
If you'd prefer to do this without any DAX, you can handle it fully in Power Query:
- Sort your data by Account and Status Date (ascending).
- Group by Account:
In Power Query: Home → Group By
Group by Account, and select “All Rows”.
Inside each group:
Add an Index column (starts at 1).
Add a custom column that references the previous row using the Index.
Here is an example to get previous status:
= if [Index] = 1 then null
else #"Previous Step"{[Index]-2}[Status]- Same logic for previous date: just replace [Status] with [Status Date].
- Expand the grouped tables back into the full table.
This gives you a clean table showing:
Current Status
Current Status Date
Previous Status
Previous Status Date
No DAX, all done in Power Query!
⭐Hope this solution helps you make the most of Power BI! If it did, click 'Mark as Solution' to help others find the right answers.
💡Found it helpful? Show some love with kudos 👍 as your support keeps our community thriving!
🚀Let’s keep building smarter, data-driven solutions together! 🚀 [Explore More]Hi vijayKaushal
Thank you for submitting your question to the Microsoft Fabric Community Forum, and thanks to GrowthNatives for sharing helpful suggestions.
Could you let us know if the suggested solution resolved your issue? This information can assist other community members facing similar challenges.
Thank you.
could you pls provide some sample data and expected output?
You're trying to track how accounts move through different statuses over time - that's a classic business scenario and totally doable in Power BI.
I'd go with the Power Query approach because it's cleaner:
Here's what actually works (I've tested this pattern many times):
First, get your data sorted right:
- Sort by Account, then Status Change Date (oldest first)
Add an index per account:
- Group by Account → Add Column → Index (starting from 0)
- This gives each status change a sequence number within each account
Create the previous status lookup:
Previous Status = let CurrentIndex = [Index], CurrentAccount = [Account] in if CurrentIndex = 0 then null else try Table.SelectRows(#"Previous Step", each [Account] = CurrentAccount and [Index] = CurrentIndex - 1)[Status]{0} otherwise null
Do the same thing for Previous Status Date, just swap [Status] for [Status Change Date].
Why Power Query instead of DAX? Performance. With DAX measures, every time someone filters or clicks something, it recalculates. With Power Query, it's done once during refresh.
Pro tip: Add a "Days in Status" calculated column too:
Days in Status = if [Next Status Date] = null then Date.From(DateTime.LocalNow()) - [Status Change Date] else [Next Status Date] - [Status Change Date]
This gives you progression timing, which is usually what people really want when tracking account status changes.
Have you got your data structured with Account, Status, and Status Change Date columns? That's all you need to make this work.
If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
This response was assisted by AI for translation and formatting purposes.Hi vijayKaushal
I wanted to check if you’ve had a chance to review the information provided. If you have any further questions, please let us know. Has your issue been resolved? If not, please share more details so we can assist you further.
Thank You.Hi vijayKaushal
We haven't received a response to our last message and wanted to follow up to see if you have found a solution. If you still need help, please share more details so we can assist you further.
Thank you.
