Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Andiko
Frequent Visitor

Difference to Previous Row related to the Date

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). 

 

Andiko_1-1728588923074.png

 

 

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

5 REPLIES 5
Andiko
Frequent Visitor

@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. 

 

Andiko_2-1728769685053.png

 

 

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. 

 

Andiko_1-1728769223707.png

 

Could you guys help me please?🙏

 

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

Andiko
Frequent Visitor

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
Frequent Visitor

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_0-1728693087020.png

 

Andiko
Frequent Visitor

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

v-zhengdxu-msft
Community Support
Community Support

Hi @Andiko 

 

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

vzhengdxumsft_0-1728613562026.png

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:

vzhengdxumsft_1-1728613606659.png

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.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

Top Solution Authors