Find the changed values of a column from two different dates based on the dates column in same table

[Anonymous]

I have a comparison report that I do in excel based on our large sales force dataset. I now want to shift the reporting to Power BI. In excel, I would have the two tables I'm comparing in separate tabs. I would then use formulas to compare the two datasets. In power BI, I have merged all the tables into one large table and added a date column to each table in order to use the date as a pointer. So all rows of one section in the merged table will have date "June 1 2017". I want to be able to compare the other columns based on each section. I know inorder to do that I would just need to filter the table based on the specified date and subtract it from another filtered section of the table in order to get the differece. I have been doing that to find the difference in revenue and likewise for the days between "close dates" NOT the date column I use as a pointer: 

Date Before = CALCULATE(MAX('Query1'[Close_Date].[Date]),FILTER ('Query1','Query1'[Date]= MIN ( 'Query1'[Date] )))
Date After = CALCULATE(MAX('Query1'[Close_Date].[Date]),FILTER ('Query1','Query1'[Date]= MAX( ( 'Query1'[Date] ))))
Days Btwn = DATEDIFF(Table1[Date Before],Table1[Date After],DAY)

I want to be able to use this method or any other simpler method to compare the change in Sales Stage of the sections of the table. Each table has the roughly the same Opportunity IDs, some added, some disappear. So I want to see if the Sales stage of an opportunity ID went from Unqualifed to Qualified. For example this section of the large table below (the Opportunity IDs are covered for confidentiality reasons):

I can not use the same forumla for this as I do for the dates or the revenue because CALCULAT() requires numerical values and Sales Stage Category is text. Any help would be appreciated!

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

First, add an Index column in Query Editor mode.

let
    Source = Excel.Workbook(File.Contents("C:\Users\xxx\Desktop\Sample Data.xlsx"), null, true),
    Query1_Sheet = Source{[Item="Query1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Query1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Opp ID", type text}, {"SS Category", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Removed Columns" = Table.RemoveColumns(#"Added Index",{"Index"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Date"}, {{"All rows", each _, type table}}),
    #"Added Index1" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1),
    #"Expanded All rows" = Table.ExpandTableColumn(#"Added Index1", "All rows", {"Opp ID", "SS Category"}, {"All rows.Opp ID", "All rows.SS Category"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded All rows",{{"All rows.Opp ID", "Opp ID"}, {"All rows.SS Category", "SS Category"}})
in
    #"Renamed Columns"

Then, to compare the Sales Stage in last date with current value, please create a calculated column with below DAX:

Previous status =
LOOKUPVALUE (
    Query1[SS Category],
    Query1[Opp ID], Query1[Opp ID],
    Query1[Index], Query1[Index] - 1
)

Best regards,

Yuliana Gu