comparing bi-annual totals in matrix table

[bunnelov]

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!

[v-alq-msft]

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

[bunnelov]

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 

[richbenmintz · Accepted 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!

[bunnelov]

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

[v-alq-msft]

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.

[parry2k]

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