Dynamic Percent Change between Semesters

[DTorres4043]

Hello, all,

I am attempting to calculate dynamic percent change from term to term on multiple measures (e.g., enrollment, course completions, GPA, etc.) for multiple demographic groups (e.g., race/ethnicity, gender, age, etc.). Below is an example table of data for a small community college: 

Gender	Spring 2021	Spring 2022	Spring 2023	Spring 2024
Female	1945	1943	1866	1927
Male	845	912	961	1125
Total	2800	2855	2830	3056

I would like to show the percent change between terms and I want the values to change based on the selection of slicers or field parameters for demographics. I have only been able to come up with the following, but it only yields the percent change between the minimum and maximum term selected:

Perc_Chng =

DIVIDE(

 CALCULATE(

 DISTINCTCOUNT('enrl_comp_succ'[Student ID - Campus]),

 FILTER('enrl_comp_succ','enrl_comp_succ'[STRM]=MAX('enrl_comp_succ'[STRM])

 )

 ),

 CALCULATE(

 DISTINCTCOUNT('enrl_comp_succ'[Student ID - Campus]),

 FILTER('enrl_comp_succ','enrl_comp_succ'[STRM]=MIN('enrl_comp_succ'[STRM])))

,0)

- 1

Thanks in advance for any assistance on this topic.

[Anonymous · Accepted solution]

Hi @DTorres4043 ,

Please try this way:
Here is my sample data:

If I understand you correctly, you are wanting to calculate the percentage difference in values between two adjacent semesters, as well as change the content of the difference calculation via slicer. Given that the display format of your term is not directly involved in the sorting calculations, I would suggest that you add an Index column to the Power Query based on each ID to facilitate the calculations.
You can use this M function to add Index column:

Table.Group(#"Changed Type", {"ID"}, {{"Count", each Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type)}})

The output is as below:

Let's first implement the calculation of the percentage difference between the values of two adjacent semesters:
I create a measure using this DAX:

Measure = 
VAR CURRENT_Value = 
CALCULATE(
    SUM('Table'[Value]),
    FILTER(
        'Table',
        'Table'[Index] = MAX('Table'[Index])
    )
)
VAR NEXT_Value = 
CALCULATE(
    SUM('Table'[Value]),
    FILTER(
        ALL('Table'),
        'Table'[Index] = MAX('Table'[Index]) + 1 && 'Table'[ID] = MAX('Table'[ID])
    )
)
RETURN
IF(
    NEXT_Value <> BLANK(),
    DIVIDE(CURRENT_Value, NEXT_Value) - 1,
    BLANK()
)

The final output is as below:

Then we come to the second goal, modifying the content of the difference calculation according to the slicer:

Measure 2 = 
VAR CURRENT_Value = 
CALCULATE(
    SUM('Table'[Value]),
    FILTER(
        ALLEXCEPT('Table', 'Table'[ethnicity], 'Table'[gender]),
        'Table'[Index] = MAX('Table'[Index])
    )
)
VAR NEXT_Value = 
CALCULATE(
    SUM('Table'[Value]),
    FILTER(
        ALLEXCEPT('Table', 'Table'[ethnicity], 'Table'[gender]),
        'Table'[Index] = MAX('Table'[Index]) + 1
    )
)
RETURN
IF(
    NEXT_Value <> BLANK(),
    DIVIDE(CURRENT_Value, NEXT_Value) - 1,
    BLANK()
)

The final output is as below (Note: Only put terms and Index into the table viusal, Otherwise the calculations will automatically apply filtering resulting in data errors):

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DTorres4043]

Thanks for all the effort you put into helping answer my dilemma, Dino. Before I get started, though, can you help me understand your initial index column creation? What is the "Changed Type" referring to; I tried entering the code as is and got an Expression.Error.

[Anonymous]

Hi @DTorres4043 ,

I'm sorry I forgot to make it clear that this is the M Function in Power Query.
Click here to go to Power Query:

Click here and put the code into this:

Like this:

Then you will get this table:

Click here to expand the table:

Then we will finally get the Index column:

Click "Close & Apply".

Best Regards,
Dino Tao

[AnalyticPulse]

try below dax and let me know if it works;
Dynamic_Perc_Change =
VAR CurrentTerm = MAX('enrl_comp_succ'[STRM])
VAR PreviousTerm = CALCULATE(MAX('enrl_comp_succ'[STRM]), ALL('enrl_comp_succ'[STRM]), 'enrl_comp_succ'[STRM] < CurrentTerm)
VAR CurrentValue = CALCULATE(SUM('enrl_comp_succ'[Enrollment]), 'enrl_comp_succ'[STRM] = CurrentTerm)
VAR PreviousValue = CALCULATE(SUM('enrl_comp_succ'[Enrollment]), 'enrl_comp_succ'[STRM] = PreviousTerm)
RETURN
IF(ISBLANK(PreviousValue), BLANK(), (CurrentValue - PreviousValue) / PreviousValue)

If this helped, Follow this blog for more insightful information about data analytics
https://analyticpulse.blogspot.com/
https://analyticpulse.blogspot.com/2024/03/superstore-sales-2022-vs-2023-year-on.html
https://analyticpulse.blogspot.com/2024/04/commercial-real-estate-portfolio.html

[DTorres4043]

Thanks for the suggestion. It did not work, however. I wonder if it could be a function of how terms are stored in the college's database. Spring 2024 is stored as 1241. Summer 2024 is stored as 1246. Fall is stored as 1248. If you notice the pattern, the preceding 1 is for the present century. The second two digits is the year: 24 is 2024, 23 is 2023, and so on. The last digit denotes the semester: 1 is spring, 6 is summer, and fall is 8.

Does that help?