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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Serdet
Post Patron
Post Patron

Subtracting columns referring to previous index

Hi,

 

I have a dataset similar to the below.

 

DateIDPercentage CompleteIndex
01/01/20221101
02/01/20221302
03/01/20221403
04/01/20222301
05/01/20222502
06/01/202221003
07/01/20223501
08/01/20223102

 

The index column has been created by grouping the ID's therefore each ID has a index for every occurance in data order.

 

I was to calculate the percentage difference from the previous index and if there is no previous index take the first percentage shown.

 

For example:

 

DateIDPercentage CompleteIndexCalculated % Change
01/01/2022110110
02/01/2022130220
03/01/2022140310

 

Any ideas on how this can be achieved?

 

Many thanks

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Serdet , a new column

[Percentage] - maxx(filter(Table, [ID] =earlier([ID]) && [Index] = earlier([Index]) -1) , [Percentage ] )

 

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@Serdet , a new column

[Percentage] - maxx(filter(Table, [ID] =earlier([ID]) && [Index] = earlier([Index]) -1) , [Percentage ] )

 

Is it possible to add to the above measure?

 

Now that I have the calculated % change. Would it be possible to compare this to another column which is (manually entered % change?

 

Ideally I would want the value in the calculated column to highlight red if it differs from the manual entered % change value.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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