Forum Discussion

preetjawaria's avatar
preetjawaria
Frequent Visitor
3 years ago
Solved

How to get the earlier value

Sample Data : Table: ID Date Stage 1411 9/22/2022 Stage 1 1411 1/31/2023 Stage 2 1411 2/3/2023 Won   I have similar data for multiple IDs, I am looking for a Column DAX cal...
  • Anonymous's avatar
    Anonymous
    3 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.