calculate the percentage change from week on week

[DharmaChinta]

 Hi Team , Need some help on DAX Measure to find data of previous record for each piece of data in a time series . Here is the sample data  , the actual requirement here is to calculate the percentage change from week on week by comparing with the previous value of the same step  i think for this we might need previous value . Please help me here .

consumerdomainnotallowed  12/8/2018 18344

consumerdomainnotallowed  12/1/2018 16724

[v-danhe-msft]

Hi @DharmaChinta,

Based on my test, you could refer to below steps to show the changed value:

Sample data:

Create two calculated columns:

Week = WEEKNUM('Table1'[Date],2)

Change value = var a=[Week]+1
return IF(ISBLANK(CALCULATE(SUM(Table1[Value]),FILTER('Table1','Table1'[Week]=a))),BLANK(),[Value]-CALCULATE(SUM(Table1[Value]),FILTER('Table1','Table1'[Week]=a)))

Result:

You could also download the pbix file to have a view.

Regards,

Daniel He

[DharmaChinta]

Thanks for your reply , the problem here is i do have a column like step name . So for each step name i need to calculate the difference with the previous week value .

stepName last4Weeks week ErrorCount
genericerror 2018-11-24 00:00:00.0000000 2018-12-15 00:00:00.0000000 207
AdminTenantInfoSystemError from AdminTrialNew 2018-11-24 00:00:00.0000000 2018-12-15 00:00:00.0000000 64

genericerror 2018-11-24 00:00:00.0000000 2018-12-08 00:00:00.0000000 235
AdminTenantInfoSystemError from AdminTrialNew 2018-11-24 00:00:00.0000000 2018-12-08 00:00:00.0000000 92

Can you please check and help me here .

[v-danhe-msft · Accepted solution]

Hi @DharmaChinta,

Based on my test, you could refer to below formula:

Create two columns:

Weeknum = WEEKNUM('Table1'[week])

Column 2 = var a=[Weeknum]+1
return CALCULATE(SUM(Table1[ErrorCount]),FILTER('Table1','Table1'[Weeknum]=a&&'Table1'[stepName]=EARLIER(Table1[stepName])))

Result:

Or you could use below measure:

Measure = var a=CALCULATE(SUM(Table1[Weeknum]))+1
return CALCULATE(SUM(Table1[ErrorCount]),FILTER(ALL('Table1'),'Table1'[Weeknum]=a&&'Table1'[stepName]=MAX(Table1[stepName])))

Result:

You could also download the pbix file to have a view.

Regards,

Daniel He

[DharmaChinta]

Man used saved my life , thanks for all your help!!!