Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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
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 .
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
Man used saved my life , thanks for all your help!!!