Forum Discussion
Helper ICompare with previous year
Sorry for posting 2 questions in 1 day.
I'm looking to create something that looks like this...
| Setting | Selected year attendance % | Previous Year's quarter attendance % | Difference % |
| Place A | 90.5% | 94.5% | -4.0% |
| Place B | 93.0% | 95.9% | -2.9% |
| Place C | 92.4% | 94.4% | -2.0% |
| Place D | 91.8% | 93.3% | -1.6% |
The data I have looks something like this...
| Setting | Quarter | Year | Possible Attendance | Actual Attendance |
| Place A | Q1 | 2021 | 2064 | 1962 |
| Place A | Q1 | 2022 | 2298 | 2198 |
| Place A | Q1 | 2023 | 2625 | 2352 |
| Place B | Q1 | 2021 | 1292 | 1250 |
| Place B | Q1 | 2022 | 1332 | 1204 |
| Place B | Q1 | 2023 | 2073 | 1944 |
| Place C | Q1 | 2021 | 2812 | 2675 |
| Place C | Q1 | 2022 | 2832 | 2698 |
| Place C | Q1 | 2023 | 2788 | 2594 |
| Place D | Q1 | 2021 | 6955 | 6748 |
| Place D | Q1 | 2022 | 7068 | 6690 |
| Place D | Q1 | 2023 | 6940 | 6658 |
It goes on to Quarters 2, 3 & 4 too.
I need year & quarter to be selectable. So if 2022 Q1 is selected, then "Previous Year attendance %" would show 2021 data for Q1 & give the difference.
Any ideas?
BassG , Try following measure
% Attend = DIVIDE(SUM(Data[Actual Attendance]), sum(Data[Possible Attendance]))
Last Year = CALCULATE([% Attend], OFFSET(-4, all(Data[Year], Data[Quarter],Data[Setting]), ORDERBY(Data[Year],asc, Data[Quarter],asc)))file is attached
Hi BassG ,
You can add two tables to create slicers:And there is no relationship between tables:
Use these DAXs to create measures:
Selected year attendance % = VAR Y = SELECTEDVALUE(Slicer[Year]) VAR Q = SELECTEDVALUE(Slicer2[Quarter]) VAR ACTUAL = SUMX(FILTER('Table', 'Table'[Year] = Y && 'Table'[Quarter] = Q), 'Table'[Actual Attendance]) VAR POSSIBLE = SUMX(FILTER('Table', 'Table'[Year] = Y && 'Table'[Quarter] = Q), 'Table'[Possible Attendance]) RETURN DIVIDE(ACTUAL, POSSIBLE)Previous Year's quarter attendance % = VAR Y = SELECTEDVALUE(Slicer[Year]) VAR Q = SELECTEDVALUE(Slicer2[Quarter]) VAR ACTUAL = SUMX(FILTER('Table', 'Table'[Year] = Y - 1 && 'Table'[Quarter] = Q), 'Table'[Actual Attendance]) VAR POSSIBLE = SUMX(FILTER('Table', 'Table'[Year] = Y - 1 && 'Table'[Quarter] = Q), 'Table'[Possible Attendance]) RETURN DIVIDE(ACTUAL, POSSIBLE)Difference % = [Selected year attendance %] - [Previous Year's quarter attendance %]The final output is as below:
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.
3 Replies
- amitchandak
Super User
BassG , Try following measure
% Attend = DIVIDE(SUM(Data[Actual Attendance]), sum(Data[Possible Attendance]))
Last Year = CALCULATE([% Attend], OFFSET(-4, all(Data[Year], Data[Quarter],Data[Setting]), ORDERBY(Data[Year],asc, Data[Quarter],asc)))file is attached
Hi BassG ,
You can add two tables to create slicers:And there is no relationship between tables:
Use these DAXs to create measures:
Selected year attendance % = VAR Y = SELECTEDVALUE(Slicer[Year]) VAR Q = SELECTEDVALUE(Slicer2[Quarter]) VAR ACTUAL = SUMX(FILTER('Table', 'Table'[Year] = Y && 'Table'[Quarter] = Q), 'Table'[Actual Attendance]) VAR POSSIBLE = SUMX(FILTER('Table', 'Table'[Year] = Y && 'Table'[Quarter] = Q), 'Table'[Possible Attendance]) RETURN DIVIDE(ACTUAL, POSSIBLE)Previous Year's quarter attendance % = VAR Y = SELECTEDVALUE(Slicer[Year]) VAR Q = SELECTEDVALUE(Slicer2[Quarter]) VAR ACTUAL = SUMX(FILTER('Table', 'Table'[Year] = Y - 1 && 'Table'[Quarter] = Q), 'Table'[Actual Attendance]) VAR POSSIBLE = SUMX(FILTER('Table', 'Table'[Year] = Y - 1 && 'Table'[Quarter] = Q), 'Table'[Possible Attendance]) RETURN DIVIDE(ACTUAL, POSSIBLE)Difference % = [Selected year attendance %] - [Previous Year's quarter attendance %]The final output is as below:
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.