compare different interval variables in time periods

[vandestra]

I would like to compare for example the number of pupils this school year with the number of pupils the previous year in the previous class. For example: last  year, there were 100 pupils in 5th grade and 200 in 6th grade. This year, there are 105 in 6th grade - the difference is +5.  We call it the "flow through" rate. I have made a formula with dax formula sameperiodlastyear, so this shows that in 6th grade are 95 fewer pupils compared to last year, in a nice graph with data from 2009 to 2022, but I need to the graph to show differently. For example in the graph below. In 2022 it looks like a disaster in the "purple" school: a lot of pupils have left their grade. But in fact, the school has gained pupils compared to the previous year in the previous grade !

[amitchandak]

@vandestra ,

You need to have a change % measure

example , With help from a separate date table  

//Only year vs Year, not a level below

This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))
diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])

or

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

[vandestra]

I need that there are two categories that are switched: date ànd grade. I will illustrate with an example.

suppose that these are the numbers of pupuls registered: (table with numbers is pasted at the end)

Then I make a calculation: in the previous year and in the previous grade.

Then one can notice on a graph that in the year 2021 something extraordinary happened for the pupils that went from the 4th grade to the 5th grade. 

Grade	2017	2018	2019	2020	2021	2022
1st	40	107	93	61	30	119
2nd	80	45	112	93	55	31
3rd	100	78	40	107	102	57
4th	20	94	80	49	115	99
5th	160	25	86	86	100	122
6th	200	164	20	95	92	105

[v-kkf-msft · Accepted solution]

Hi @vandestra ,

1. Please add the index column.

2. Use the Ctrl key to select both the Grade and Index columns, then unpivot the other columns.

3. Create the measure.

Diff = 
VAR LastGradeYear =
    CALCULATE (
        SUM ( 'Table'[Number] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Index]
                = MAX ( 'Table'[Index] ) - 1
                && 'Table'[Year]
                    = MAX ( 'Table'[Year] ) - 1
        )
    )
RETURN
    IF ( LastGradeYear <> BLANK (), SUM ( 'Table'[Number] ) - LastGradeYear )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.