Forum Discussion

vijayKaushal's avatar
vijayKaushal
Regular Visitor
1 year ago
Solved

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!

Tips and Tricks
  • GrowthNatives's avatar
    GrowthNatives
    1 year ago

    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 :

    1. In Power Query, group your data by Account and add an Index column to track the order of changes.

       

    2. 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:

    1. Sort your data by Account and Status Date (ascending).
    2. Group by Account:

      • In Power Query: Home → Group By

      • Group by Account, and select “All Rows”.

    3. 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].
    4. 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

  • GrowthNatives's avatar
    GrowthNatives
    Super User
    1 year ago

    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 :

    1. In Power Query, group your data by Account and add an Index column to track the order of changes.

       

    2. 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:

    1. Sort your data by Account and Status Date (ascending).
    2. Group by Account:

      • In Power Query: Home → Group By

      • Group by Account, and select “All Rows”.

    3. 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].
    4. 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]

  • v-karpurapud's avatar
    v-karpurapud
    Community Support
    1 year ago

    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.

  • burakkaragoz's avatar
    burakkaragoz
    Super User
    1 year ago

    vijayKaushal ,

    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):

    1. First, get your data sorted right:

      • Sort by Account, then Status Change Date (oldest first)

    2. 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

    3. 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.

  • v-karpurapud's avatar
    v-karpurapud
    Community Support
    1 year ago

    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.

  • v-karpurapud's avatar
    v-karpurapud
    Community Support
    1 year ago

    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.