Data refresh impact on existing data

[jgodden]

Hi,

I have a table tracking the status of new accounts including a field for their status. Each week the table is updated and if a status changes, then a new line is added to the sheet with a new status. I need to show a visual for the accounts where the status has changed. What would be the best method for this?

[KGrice · Accepted solution]

This will partially depend on what you have in your data to indicate the status changes over time. Is there a date for each status?

Here's one way to do it using some made up data below. Before using Close and Apply to load the data, I sorted the data first by Account and then by StatusDate, and then added an Index column. This will allow you to see an ordered, row-by-row view of the changes as they occur to each account.

Then I added a new column in the Data view with the following formula:

StatusChanged = IF(
	AND(TableName[Status]<>LOOKUPVALUE(TableName[Status], TableName[Index], TableName[Index]-1, TableName[Account], TableName[Account])
		,NOT(ISBLANK(LOOKUPVALUE(TableName[Status], TableName[Index], TableName[Index]-1, TableName[Account], TableName[Account])))
	), 1, 0)

That will check the previous status against the current status, and make sure the previous status wasn't blank, i.e., there was no previous status because it's the first entry for that account.

You can then use that StatusChanged column as a filter in your visuals. Use TRUE/FALSE if you prefer that over the 1/0 values. You could also right-click the Index column and choose Hide in Report View since it won't mean anything to anybody.