cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

comparing bi-annual totals in matrix table

Hello,

I am working on a report that compares (depending on a slicer selection) fall to fall or winter to winter enrollment in a matrix chart that also includes department and major. I am having trouble with computing the annual % change for fall versus winter.

I have these two measures for fall, and it works. But for winter I need to compare 2020 and 2019.

I have tried to create a calendar table that uses a specific date for each of the terms 8/1/YYYY for Fall, 1/1/YYYY for winter and use this calculated measure, but it is not working:

This is also not working:
VAR __PREV_YEAR =
CALCULATE(
)
RETURN
DIVIDE(
__PREV_YEAR
)

I am stumped, any suggestions?
Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Solution Sage

Hi @bunnelov,

Without changing your data model you could modify your Total Enrollment measures as follows

``````Total Enrollment TY =
var maxYear = CALCULATE(max('Enrollment'[Year]), ALL(Enrollment[Year]))
return

Total Enrollment LY =
var maxYear = CALCULATE(max('Enrollment'[Year]), ALL(Enrollment[Year]))
return

Total Enrollment 5Y =
var maxYear = CALCULATE(max('Enrollment'[Year]), ALL(Enrollment[Year]))
return

now you may want to think about joing your date table to the fact and creating more of a star schema, but the formulas above should work with the single table that your data is coming from

Thanks,

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

6 REPLIES 6
Highlighted
Super User IV

@bunnelov weird, expect it to work, can you share pbix file, remove sensitive info before sharing.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Highlighted
Community Support

Hi, @bunnelov

Based on your description, I assume that the fall include September, Octover, November and the winter inclue December, January(next year), Feburary(next year). I created data to reproduce your scenario.

Table:

Calendar(a calculated table):

``Calendar = CALENDARAUTO()``

There is a relationship between two tables. You may create calculated columns and measures as below.

``````Calculated column:
Month = MONTH('Calendar'[Date])

Year = YEAR('Calendar'[Date])

Year-Season =
IF(
[Month] in {1,2},
[Year]-1&"winter",
IF(
[Month] in {9,10,11},
[Year]&"fall",
IF(
[Month]=12,
[Year]&"winter"
)
)
)

Measures:

_PREV_YEAR =
var _PREV_YEAR =
CALCULATE(
)
return
_PREV_YEAR

Result =
var _PREV_YEAR =
CALCULATE(
)
return
DIVIDE(
[_PREV_YEAR]
)``````

Result:

Best Regards

Allan

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

Highlighted
Community Support

Hi, @bunnelov

If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.

Best Regards

Allan

Highlighted
Frequent Visitor

Hello Allan,

Thank you for your advice. I am not able to get it to work. I am attaching the pbix file with some examples of what I'm trying to do and the recommendations you suggested to try so you can see the results.

Thank you!

Highlighted
Solution Sage

Hi @bunnelov,

Without changing your data model you could modify your Total Enrollment measures as follows

``````Total Enrollment TY =
var maxYear = CALCULATE(max('Enrollment'[Year]), ALL(Enrollment[Year]))
return

Total Enrollment LY =
var maxYear = CALCULATE(max('Enrollment'[Year]), ALL(Enrollment[Year]))
return

Total Enrollment 5Y =
var maxYear = CALCULATE(max('Enrollment'[Year]), ALL(Enrollment[Year]))
return

now you may want to think about joing your date table to the fact and creating more of a star schema, but the formulas above should work with the single table that your data is coming from

Thanks,

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Highlighted
Frequent Visitor

Thank you @richbenmintz !! This worked perfectly, I appreciate the simple solution.

Announcements

Power Platform Community Conference

Check out the on demand sessions that are available now!

Microsoft Power Platform Communities

Check out the Winners!

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors