Forum Discussion

Anonymous's avatar
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 cr...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Tahreem24's avatar
    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

     

     

PVO3's avatar
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's avatar
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's avatar
    PVO3
    Impactful Individual
    4 years ago

    You can fix this in PQ or DAX.

    For PQ solution check this.