Difference to Previous Row related to the Date

[Andiko]

Hello!

I have a Meassure called [Increase] which I need it to:

1. Calculate the difference from previous Row for the Column [Total Representatives Needed]

** This should be calculated for the Dates that exist in the InputDB ONLY. This entries are reflected in the [Cumulative Recurring Revenue] column

I have constructed this using the formula below: 

=[Total Representatives Needed] -

CALCULATE(
	[Total Representatives Needed],
	
		FILTER(
		all('Calendar'),
		'Calendar'[Date] <= max(InputDB[Close_Date])
	),
	
	TOPN(1, 'Calendar','Calendar'[Date],DESC)
)

However, as you can see in the image below, it is not working since the values are incorrect (the value highlited with Orange is the expected value) and it is giving me values for all the dates from the Calendar Table (even if they do not exist in the InputDB). 

I have tried many options using the EDATE function, SUMX, SUM, Etc. But, either the values are incorrect, or does not solve the problem with the dates. 

Nothing seems to work. Thus, I would like to get your help please!

I am attaching the file I am using here: Test-File 3

[Andiko]

@v-zhengdxu-msft, @shafiz_p, I was able to create the formula below that is giving me the difference to previous row and I named increase.

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

[Andiko]

Hi @shafiz_p, can you please help me with this? 🙏

I have tried so many things and nothing seem to work. Thus, I would highly appreciate your help!

[Andiko]

Hi @v-zhengdxu-msft, thank you so much for your help!

I have applied the formula you have suggested for the column Increase, but it did not work (please refer to the picture attaches). Could you please help me?

-Here is the doc: Test File

- Here is the formula applied: 

VAR _currentDate =MAX (InputDB[Close_Date])

VAR _previousDate =CALCULATE (
        MAX (InputDB[Close_Date]),
        FILTER (ALL(InputDB), InputDB[Close_Date] < _currentDate )
        )
        
RETURN


CALCULATE (
        [Total Representatives Needed],
        FILTER (ALL(InputDB), InputDB[Close_Date] = _previousDate )
    )

- Here is the Image: The Increase Column should be equal to the shifted Total Representatives Needed

** Note: I am having some problems with the Total Representatives Needed column as well. I have asked for help in Here

[Andiko]

@shafiz_p, do you think you can take a look please? 🙏

[v-zhengdxu-msft]

Hi @Andiko 

Given I don't know how you create your matrix, I'll share a sample set as a reference.

Then add a measure:

Previous =
VAR _currentDate =
    MAX ( 'Table'[Date] )
VAR _previousDate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] < _currentDate )
    )
RETURN
    CALCULATE (
        [Measure],
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] = _previousDate )
    )

The result is as follow:

If the above one can't help you, please share your .pbix file(exclude sensitive data), so that we can help you better.

Best Regards

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