Problem with Difference to Previous Row and shifted Dates

[Andiko]

 I was able to create the formula below that is giving me the difference to previous row and I namedincrease. This formula should Calculate the difference from previous Row for the Column [Total Representatives Needed]

VAR CurrentDate = MAX(InputDB[Close_Date])

VAR PreviousDate = CALCULATE(
	MAX(InputDB[Close_Date]),
	
	FILTER(
		ALL('Calendar'),
		'Calendar'[Date] < CurrentDate
		)
	)

VAR CurrentValue =[Total Representatives Needed]

VAR PreviousValue =CALCULATE(
	
	[Total Representatives Needed],
	'Calendar'[Date] = PreviousDate
	)

RETURN

IF( ISBLANK(PreviousValue), BLANK(), CurrentValue - PreviousValue)

However, it works only with the entries that have the Close_date. But, due to the nature of the calculations needed, the column Total Representatives Needed also has data for the same dates of the Close_date but shifted 1 year in the future. The shifting of those dates made by following this thread.

Now, the problems are:

1. I am unable to find a way to make the Increase formula to work for all the dates listed.

** As you can see in the first image, everything works perfectly until there is data for the entries with the Close_date shifted. 

2. The agregation does not seem to work

** ** As you can see in the second image, the values are correct when the table is expanded and all the dates are shown, but when the table is comprised, then the numbers make no sense. 

Could you guys help me please?

I am attaching the file I am using here: Test File 3.4

[Anonymous]

Hi @Andiko ,
You can  use the sumx function to iterate over the table and calculate the sum of the differences:

SUMX(
    InputDB,
    VAR CurrentDate = InputDB[Close_Date]
    VAR PreviousDate = CALCULATE(
        MAX(InputDB[Close_Date]),
        FILTER(
            ALL('Calendar'),
            'Calendar'[Date] < CurrentDate
        )
    )
    VAR CurrentValue = InputDB[Total Representatives Needed]
    VAR PreviousValue = CALCULATE(
        [Total Representatives Needed],
        'Calendar'[Date] = PreviousDate
    )
    RETURN
    IF(ISBLANK(PreviousValue), BLANK(), CurrentValue - PreviousValue)
)

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly