DAX calculation for sum of values by catgory divided by previous value and two previous values

[withnoe]

I am new to BI and am trying to figure out a DAX formula to compute a precentage change.  I have searched quite a bit for an a way to do this but all I come up with is ways to do it by dates or by static values.  The term field will change as time goes on.  The query will return the last 3 terms.  Below is an example table of the data.

term	name	Hours
202241	Joe	3
202241	Sally	5
202241	Fred	4
202341	Suzie	3
202341	John	6
202341	George	4
202441	Kile	5
202441	Brenda	8
202441	Kim	5

What I would like get a measure that will give the percentage change from the sum of the hours per term compared from the first term to the second and then from the first to the third.  For example sum of the hours for term 202241 = 12 , 202341 = 13, 202441 = 18.  The terms will always be 100 away from the next one.  For example 202441 - 202341 = 100.  The results that I am looking for are:

1 YR Percentage change from 202241 to 202341 = 12/13     8% change

2 YR Percentage change from 202241 to 202441 = 12/18     34% change

[PhilipTreacy · Accepted solution]

Hi @withnoe 

Download PBIX file with examples below

OK, that doesn't complicate things much actually, you can just use a slicer to get the results. 

I assume that is ok as you haven't provided exactly what you want as a result.  And if you have lots of colleges I'm assuming you don't want loads of card visuals for each college?  You can just select the college in the slicer:

The measures for each change are

1 Year Change = ([Sum Hours Previous Term] - [Sum Hours 2 Terms Ago]) / [Sum Hours 2 Terms Ago]

2 Year Change = ([Sum Hours Latest Term] - [Sum Hours 2 Terms Ago]) / [Sum Hours 2 Terms Ago]

and to calculate the sum of hours per term, same as before

Sum Hours Latest Term = CALCULATE(SUM('DataTable'[Hours]), 'DataTable'[term] = MAX('DataTable'[term]))

Sum Hours Previous Term = CALCULATE(SUM('DataTable'[Hours]), 'DataTable'[term] = MAX('DataTable'[term]) - 100)

Sum Hours 2 Terms Ago = CALCULATE(SUM('DataTable'[Hours]), 'DataTable'[term] = MAX('DataTable'[term]) - 200)

Regards

Phil

[Kedar_Pande]

@withnoe 

Create a Sum of Hours Measure

TotalHours = SUM('Table'[Hours])

Create a Percentage Change Measure:

PercentageChange =
VAR CurrentTerm = MAX('Table'[Term])
VAR PreviousTerm1 = CurrentTerm - 100
VAR PreviousTerm2 = CurrentTerm - 200
VAR HoursCurrent = CALCULATE([TotalHours], 'Table'[Term] = CurrentTerm)
VAR HoursPrev1 = CALCULATE([TotalHours], 'Table'[Term] = PreviousTerm1)
VAR HoursPrev2 = CALCULATE([TotalHours], 'Table'[Term] = PreviousTerm2)

RETURN
SWITCH (
TRUE(),
HoursPrev1 <> 0, (HoursCurrent - HoursPrev1) / HoursPrev1,
HoursPrev2 <> 0, (HoursCurrent - HoursPrev2) / HoursPrev2,
BLANK()
)

You can format the result as a percentage in the measure settings by selecting the percentage format in Power BI.

[PhilipTreacy]

Hi @withnoe 

Download PBIX file with example below

Create these measures

Sum Hours Latest Term = CALCULATE(SUM('DataTable'[Hours]), 'DataTable'[term] = MAX('DataTable'[term]))

Sum Hours Previous Term = CALCULATE(SUM('DataTable'[Hours]), 'DataTable'[term] = MAX('DataTable'[term]) - 100)

Sum Hours 2 Terms Ago = CALCULATE(SUM('DataTable'[Hours]), 'DataTable'[term] = MAX('DataTable'[term]) - 200)

The results you want are then calculated with these measures (Format both of these as %)

Change 1 = 1 - ([Sum Hours 2 Terms Ago] / [Sum Hours Previous Term])

Change 2 = 1- ([Sum Hours 2 Terms Ago] / [Sum Hours Latest Term])

Regards

Phil

[withnoe]

Hi Philip,

This answers my question.  However, I forgot an important part in my simplified model.  I also have a column with a college code in it.  So the sum of the hours needs to be subgrouped by college. So it would be the sum of hours by term then by college.   The only way I could see it working is to create  sum of the latest hours by each college which would be a lot of measures.  However, the issue is that the number of colleges may change so I cannot have static values for those in a measure.

[PhilipTreacy]

@withnoe 

Please supply some sample data to illustrate what you are describing.  Can't write code for tables/data I can't see!

Thanks

Phil

[withnoe]

Here is the table revision with the added college column:

term	name	college	Hours
202241	Joe	12	3
202241	Sally	12	5
202241	Fred	14	4
202341	Suzie	14	3
202341	John	12	6
202341	George	14	4
202441	Kile	14	5
202441	Brenda	12	8
202441	Kim	12	5

Here is what the result of combining these per term per college would be:

term	college	sum of hours
202241	12	8
202241	14	4
202341	12	6
202341	14	9
202441	12	12
202441	14	5

Does this help?