cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular 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. 

Total Enrollment TY = Calculate(SUM(Enrollment[Headcount]),Enrollment[Year]=2019)
Total Enrollment LY = Calculate(SUM(Enrollment[Headcount]),Enrollment[Year]=2018)
 
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: 
 
current_year = CALCULATE(SUM('Enrollment'[Headcount]))
prior_year = Calculate(SUM(Enrollment[Headcount]),DATEADD('Calendar'[Date],-1,YEAR))
 
This is also not working:
Headcount 1Y% =
VAR __PREV_YEAR =
    CALCULATE(
        SUM('Enrollment'[Headcount]),
        DATEADD('Calendar'[Date], -1, YEAR)
    )
RETURN
    DIVIDE(
        SUM('Enrollment'[Headcount]) - __PREV_YEAR,
        __PREV_YEAR
    )
 
I am stumped, any suggestions?
Thank you!
 
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Solution Sage
Solution Sage

Re: comparing bi-annual totals in matrix table

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!

View solution in original post

6 REPLIES 6
Highlighted
Super User VII
Super User VII

Re: comparing bi-annual totals in matrix table

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






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.





Highlighted
Community Support
Community Support

Re: comparing bi-annual totals in matrix table

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:

f1.png

 

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:

f2.png

 

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
Community Support

Re: comparing bi-annual totals in matrix table

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
Regular Visitor

Re: comparing bi-annual totals in matrix table

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!

Link to pbix example file 

 

@parry2k@v-alq-msft 

Highlighted
Solution Sage
Solution Sage

Re: comparing bi-annual totals in matrix table

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!

View solution in original post

Highlighted
Regular Visitor

Re: comparing bi-annual totals in matrix table

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

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Kudoed Authors