Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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
Calculate(SUM(Enrollment[Headcount]),Enrollment[Year]=maxYear)
Total Enrollment LY =
var maxYear = CALCULATE(max('Enrollment'[Year]), ALL(Enrollment[Year]))
return
Calculate(SUM(Enrollment[Headcount]),Enrollment[Year]=maxYear -1)
Total Enrollment 5Y =
var maxYear = CALCULATE(max('Enrollment'[Year]), ALL(Enrollment[Year]))
return
Calculate(SUM(Enrollment[Headcount]),Enrollment[Year]=maxYear -5)
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 I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!
Proud to be a Super User!
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
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!
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
Calculate(SUM(Enrollment[Headcount]),Enrollment[Year]=maxYear)
Total Enrollment LY =
var maxYear = CALCULATE(max('Enrollment'[Year]), ALL(Enrollment[Year]))
return
Calculate(SUM(Enrollment[Headcount]),Enrollment[Year]=maxYear -1)
Total Enrollment 5Y =
var maxYear = CALCULATE(max('Enrollment'[Year]), ALL(Enrollment[Year]))
return
Calculate(SUM(Enrollment[Headcount]),Enrollment[Year]=maxYear -5)
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 I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!
Proud to be a Super User!
Thank you @richbenmintz !! This worked perfectly, I appreciate the simple solution.
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:
current_year = SUM('Table'[Headcount])
_PREV_YEAR =
var _PREV_YEAR =
CALCULATE(
SUM('Table'[Headcount]),
DATEADD('Table'[Date],-1,YEAR)
)
return
_PREV_YEAR
Result =
var _PREV_YEAR =
CALCULATE(
SUM('Table'[Headcount]),
DATEADD('Table'[Date],-1,YEAR)
)
return
DIVIDE(
SUM('Table'[Headcount])-_PREV_YEAR,
[_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.
@bunnelov weird, expect it to work, can you share pbix file, remove sensitive info before sharing.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |