The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Solved! Go to Solution.
@Anonymous ,
For any time intelligence function, you could implement a custom DAX formula.
@Anonymous ,
For any time intelligence function, you could implement a custom DAX formula.
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!
@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.
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:
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
139 | |
110 | |
107 | |
76 | |
62 |
User | Count |
---|---|
270 | |
129 | |
123 | |
100 | |
92 |