I want to show current row of data and then show what has changed based on an effective from date

[pauljohnson]

Hi,

I am trying to show when changes were made to a set of data and what the changes were, i have each line i need in a table similar to the below and i want to only show rows where a new effective from date has been uploaded in the last 14 days, i have this by filtering on the relative date on my visualisation, but i now want to show the corresponding row, that was the previous date it was last changed. Hope i am making sense.

My expected outcome would be to only show the top 6 rows as the 30/08/2021 rows are newly changed and the 10/05/2021 were what it was previously, if this could be ordered as well to show the newest change and then underneath the previous data for each row that would be a bonus. (i have 1000's of rows and around 50 or so changes are made every week, the effective from date is always the Monday of each week)

Thanks Paul J

Site	Job Title	Effective From	EMP Type	Pre/Post/Emp	Rate Type	Pay	Charge	Pay OT	Charge OT
Paul J Test site	Paul Test Card	30/08/2021	PAYE	Pre Parity	OT 1	11.00	13.00	11.00	13.00
Paul J Test site	Paul Test Card	30/08/2021	PAYE	Pre Parity	OT 2	12.00	14.00	12.00	14.00
Paul J Test site	Paul Test Card	30/08/2021	PAYE	Pre Parity	Weekday	10.00	12.00	10.50	12.50
Paul J Test site	Paul Test Card	10/05/2021	PAYE	Pre Parity	OT 1	10.00	12.00	10.67	12.67
Paul J Test site	Paul Test Card	10/05/2021	PAYE	Pre Parity	OT 2	9.50	11.50	10.42	12.42
Paul J Test site	Paul Test Card	10/05/2021	PAYE	Pre Parity	Weekday	9.00	11.00	10.17	12.17
Paul J Test site	Paul Test Card	23/11/2020	PAYE	Pre Parity	OT 1	8.50	10.50	9.92	11.92
Paul J Test site	Paul Test Card	23/11/2020	PAYE	Pre Parity	OT 2	8.00	10.00	9.67	11.67
Paul J Test site	Paul Test Card	23/11/2020	PAYE	Pre Parity	Weekday	7.50	9.50	9.42	11.42

[edhans]

Hi @pauljohnson - you didn't provide a visual of what your expected results are, nor a more complete set of data, so I faked some data in with yours, and then tried to read what you wanted, but as they say, a picture is worth a thousand words. 😁

The measure for Item Rank is:

Item Rank = 
RANKX(
    ALLEXCEPT(
        'Table',
        'Table'[Site],
        'Table'[Job Title]
    ),
    CALCULATE(
        SELECTEDVALUE( 'Table'[Effective From] )
    ),
    ,
    ,
    DENSE
)

Then I opened the Filter pane and told it to only show data where the Item Rank was <= 2 to show the top two items.

Finally, in this Table visual, selected the Site to sort by it, then I held the SHIFT key and subsequently selected the Rate Type, then finally the Effective From field (twice) to make that one descending.

If that isn't what you want please provide more info. Specifically more than just Paul's data so we can test it against multiple people, and mock up your desired results in Excel and screencap that here for us.

[pauljohnson]

Hi @edhans many thanks for your speedy response, i understand the concept now and believe the solution would work was it not for a circular dependancy, i have no relationships to the table, and the columns used are not calculated columns, i have looked looked and looked and cannot figure out why even after reading an article on this, any advice would be helpful.

below is the code i am using 

Change Rank = 
RANKX(
    ALLEXCEPT(
        Changes,
        Changes[Site id],
        Changes[Template]
    ),
    CALCULATE(
        SELECTEDVALUE(Changes[Effective From - Copy])
    ),
    ,
    ,
    Dense
)

[edhans]

Not sure the issue exactly. The formula I gave you is a measure, not a calculated column.