calculate difference in score from previous score for multiple customers

[Anonymous]

Hi,

I want to create the % change column by taking the difference of the last score for each 'Dm Site Name' to previous score. This could be done by TaskCreatedOn or TaskID. Please help!

TaskID	Dm Site Name	TaskCreatedOn	StatusName	Total %	Change
111	Headquarters A	10/7/19	Completed (remediation identified)	73%	-
222	Flagship A	10/7/19	Completed (no remediation)	89%	-
333	Flagship A	12/7/19	Completed (remediation identified)	88%	-1%
444	Headquarters A	11/11/19	Completed (no remediation)	72%	-1%
555	Headquarters A	2/24/20	In Progress	75%	3%

[Greg_Deckler · Accepted solution]

So maybe:

Column =
  VAR __SiteName = [Dim Site Name]
  VAR __PreviousID = 
    MAXX(
      FILTER(
        'Table',
        [TaskID] < EARLIER([TaskID]) &&
          [Dim Site Name] = __SiteName
      ),
      [TaskID]
    )
  VAR __PreviousTotal = 
    MAXX(
      FILTER(
        'Table',
        [TaskID = __PreviousID &&
          [Dim Site Name] = __SiteName
      ),
      [Total %]
    )
RETURN
  [Total %] - __PreviousTotal