Forum Discussion

Anonymous

Not applicable

4 years ago

Solved

Calculating Percentage Change

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?

Tips and Tricks

Tutorial Requests

Tahreem24
4 years ago
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

4 Replies

PVO3
Impactful Individual
4 years ago
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
  Not applicable
  4 years ago
  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
    Impactful Individual
    4 years ago
    You can fix this in PQ or DAX.
    
    For PQ solution check this.
Tahreem24
Super User
4 years ago
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