Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 !
Solved! Go to 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.
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]))
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 |
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.
User | Count |
---|---|
102 | |
92 | |
85 | |
78 | |
71 |
User | Count |
---|---|
113 | |
104 | |
101 | |
73 | |
65 |