Compare two rows of same column of one table and return only the difference

[giramswa]

Hi All,

I have Monthly_Project_Summary_History table as shown below:

SNAPSHOT_WEEK	SUMMARY	author	summary_author_email	Project_ID
May 29, 2010	Old Summary	Abc Xyz	abc.xyz@company.com	P_1
June 29, 2010	Old Summary	Abc Xyz	abc.xyz@company.com	P_1
July 29, 2010	New Summary	Abc Xyz	abc.xyz@company.com	P_1
August 29, 2010	New Summary	Abc Xyz	abc.xyz@company.com	P_1
September 29, 2010	Final Summary	Nop Qrs	nop.qrs@company.com	P_1

Above is an example of records for a single project.
This table captures monthaly summary of every Open project. I need to create a report to show Summary on only those snapshot_week when the Summary was changed. So expected outcome is as below:

SNAPSHOT_WEEK	SUMMARY	author	summary_author_email	Project_ID
May 29, 2010	Old Summary	Abc Xyz	abc.xyz@company.com	P_1
July 29, 2010	New Summary	Abc Xyz	abc.xyz@company.com	P_1
September 29, 2010	Final Summary	Nop Qrs	nop.qrs@company.com	P_1

How to achieve this?

[Gokul_G16]

Hey @giramswa ,


According to you request for this you can dynamically douvable from Power Query only.
Step 1; Concatenate =
Date  & Summary & Author ID.

Step 2; remove duplicates.
Just select the concatenate column and then click the Re-Duplicate.

Step 3;
Close and apply. 
finally you got it.

Thanks 
Gokul.

[giramswa]

My table is Calculated table (created via DAX by joining other two tables and selecting only required columns), so I cannot go to Power Query editor for this. Thats why I am looking for a DAX to achieve final solution.

[FreemanZ]

hi @giramswa 

what do you mean by "change" in "show Summary on only those snapshot_week when the Summary was changed"?

[giramswa]

Hi @FreemanZ 

If you look at SUMMARY column in my table, values are same for first two months and then they are same for third are fourth month. I want to shows rows with unique SUMMARY on my report.

[FreemanZ · Accepted solution]

hi @giramswa 

if that is the case, try to create a calculated table like this:

SumTable =

ADDCOLUMNS(

    SUMMARIZE(

        TableName,

        TableName[Summary],

        TableName[author],

        TableName[summary_author_email],

        TableName[Project_ID]

    ),

    "SNAPSHOT_WEEK",

    CALCULATE(MAX(TableName[SNAPSHOT_WEEK]))

)

i tried and it worked like this:

[giramswa]

Thank you so much, this was really helpful.

[FreemanZ]

hi @giramswa 

you may also plot a table visual with zero code. like this: