Relating Terms (Educational Terms i.e FALL2019) to previous terms (FALL18)

[Anonymous]

I am attempting to build an enrollment report. I need to relate these terms so I can calculate enrollment numbers year over year by term. I am struggling to find a way to say that the previous term for FALL2019 is FALL2018, etc. 

I have to report total enrollment with the difference year over year by term. This works if I pull all data from the table, but there are times in which there is both summer and fall data in the table for that time period. 

[v-chuncz-msft · Accepted solution]

@Anonymous ,

For any time intelligence function, you could implement a custom DAX formula.

• https://community.powerbi.com/t5/Desktop/Year-on-year-for-previous-reporting-period/m-p/326661#M145766
• https://community.powerbi.com/t5/Desktop/Percentange-Difference-on-selected-Years-only/m-p/589960#M279871
• https://www.sqlbi.com/articles/time-intelligence-in-power-bi-desktop/

[synergised]

It might be easier if you think of each term as two date components.   Year and (Spring, Summer , Fall) or a start period (1/1/2019, 5/1/2019, 9/1/2019).

Using the startperioddate - you can just subtract one year and match on the month in your DAX calc statement to get the previous term.

By splitting them into two components -you should be able to use a matrix grid

Matrix         2017     2018

Spring           ##        ##

Summer        ##        ##

Fall                ##        ##

We created several control tables in sql with the current <> previous association and the appropriate sort fields so we could always display the terms in the correct order.  We do performance modeling and forcasting down to the week and sometimes day level for target/goal reporting.

RefExpectedStartTermId RefExpectedStartTermName StartPeriodDate
32                                    Spring 2018                           2018-01-01
55                                    Summer 2018                        2018-05-01
8                                      Fall 2018                                2018-09-01
33                                    Spring 2019                           2019-01-01
57                                    Summer 2019                        2019-05-01
9                                      Fall 2019                                2019-09-01
34                                    Spring 2020                           2020-01-01
77                                    Summer 2020                        2020-05-01
78                                    Fall 2020                                2020-09-01

RefExpectedStartTermId RefExpectedStartTermPrevId RefExpectedStartTermPrevName
32                                    31                                           Spring 2017
55                                    40                                           Summer 2017
8                                      7                                             Fall 2017
33                                    32                                           Spring 2018
57                                    55                                           Summer 2018
9                                      8                                             Fall 2018
34                                    33                                           Spring 2019
77                                    57                                           Summer 2019
78                                    9                                             Fall 2019

Have fun! 

[Anonymous]

@synergised I think what I'm really asking then is what does the below look like? I'm having issues finding how to match on month. 

---

@synergised wrote:

Using the startperioddate - you can just subtract one year and match on the month in your DAX calc statement to get the previous term.

[Anonymous]

I feel like im getting somewhere, but I'm still in need of some help. I'm fairly new to Power BI and I'm the only one in my institution who currently uses the product. This one just has me stumped. 

I'm going to go with the startperioddate plan because I already have tables set up for term start and end dates. Where my issue lies is this:

EnrollLY = CALCULATE(COUNT(PRODHeadCountHistoricalReporting[RegisteredTerm]), PARALLELPERIOD(ODS_TERMS[TERM_START_DATE],-1,year))

And I'm getting same year data back:

Term table example:

Relationship: