topic Re: Calculate percentage change for a measure value in DAX Commands and Tips

Calculate percentage change for a measure value

Anonymous — Wed, 04 Dec 2019 08:54:50 GMT

So here is the scenario where I have created a measure by summing of column from different tables. Like

total count = TableA[Class A count] + tableB[class B count]

and I have a date coulmn which is related to both table A and Table B.

Now I would like to calculate the percentage increase or decrease per day of total count. Which will be

Percentage Increase = (Total count for today- total count for previous day)/ total count for previous day

How to calculate this for each date?

Thanks,Ankku

Re: Calculate percentage change for a measure value

Anonymous — Wed, 04 Dec 2019 13:02:59 GMT

Anonymous can you please share some dummy data to reproduce the scenario. also the type of relationship beween these two tables

Measure 2 = VAR _currDate = MAX(Table[Date]) VAR _prevDate = CALCULATE(MAX(Table[Date]),Table[Date]<_currDate) VAR _prevValue = CALCULATE([YourMeasure],Table[Date]=_prevDate) RETURN DIVIDE(([YourMeasure]-_prevValue),_prevValue,0)

Re: Calculate percentage change for a measure value

Anonymous — Wed, 04 Dec 2019 13:29:48 GMT

So I have something like this:

Table A
Class ID Sports count Date

1 12 25/09/2019

1 14 26/09/2019

2 32 25/09/2019

2 02 26/09/2019

Table B
Class ID Academic count Date

1 10 25/09/2019

1 12 26/09/2019

2 39 25/09/2019

2 42 26/09/2019

So now I need to calculate the growth % for different class ID for everyday. Like for class ID 1 the growth will be total count on 26th that is ( 12+ 14) - total count on 25th (10+ 12) which is 26-22 = 4 and then % increase will be (4/22)*100

Hope this make my requiremnet clear. Thanks again

Ankit

Re: Calculate percentage change for a measure value

Anonymous — Thu, 05 Dec 2019 05:56:29 GMT

Anonymous I assume there is one to one relationship between this two tables. If not create a key column in both the table by merging date and id

Key = CONCATENATE(FORMAT(TableA[Date],"yyyyMMdd"),TableA[Class ID])

Create similar column in table B and create one to one relationship
Now create a measure to get total count i.e. academic and sports

Measure = SUMX(TableA,TableA[Sports count])+SUMX(TableB,TableB[Academic count])

Finally create a measure to calculate percentage change

Measure 2 = VAR _currdate = MAX(TableA[Date]) VAR _prevDate = CALCULATE(MAX(TableA[Date]),TableA[Date]<_currdate,ALLEXCEPT(TableA,TableA[Class ID])) VAR _prevCount = CALCULATE([Measure],FILTER(ALLEXCEPT(TableA,TableA[Class ID]),TableA[Date]=_prevDate)) RETURN DIVIDE(([Measure]-_prevCount),_prevCount,0)