Calculating Percentage Change

[Anonymous]

Hi,

I have a dataset similar to the below.

Date	Job ID	Job Hours	Percentage Achieved
01/01/2022	1	100	10%
02/01/2022	1	100	20%
03/01/2022	1	100	50%

I need to create a calculated colum showing the percentage difference from each job based upon the previous date entry.

Below is an example of the required outcome.

Date	Job ID	Job Hours	Percentage Achieved	Percentage Difference (Calculated column)
01/01/2022	1	100	10%	10%
02/01/2022	1	100	20%	10%
03/01/2022	1	100	50%	30%

Any ideas on how this can be achieved?

[Tahreem24 · Accepted solution]

@Anonymous Try this measure:

Measure =

VAR Prev_Row = CALCULATE(SUM('Table'[Percentage Achieved]),FILTER(ALL('Table'),'Table'[Date]<=MAX('Table'[Date])))

VAR Diff_ = Prev_Row-SUM('Table'[Percentage Achieved])

VAR Result = IF(Diff_=0,SUM('Table'[Percentage Achieved]),Diff_)

RETURN Result

[PVO3]

First add an index column in Power Query.

Then add DAX column like below

Column =

VAR _index = 'Table'[Index]-1

RETURN

'Table'[Percentage Achieved] -

CALCULATE(MIN('Table'[Percentage Achieved]),FILTER('Table','Table'[Index] = _index))

[Anonymous]

Thanks for the quick response. How ever my dataset includes an abundence of jobs. For example, rows for 01/01/2022 could countain 50 jobs, indexing would not refer back to specific job ID's.

[PVO3]

You can fix this in PQ or DAX.

For PQ solution check this.