YTD Registrations

Power BI
1

First, for context, an academic year starts on July 1 and ends on June 30 of the following year.

What I was originally doing was trying to use Time Intelligence using the below formula.

Registrations YTD = 
CALCULATE(
    [Registrations],
    DATESYTD(
        'Dates V2'[Date],
        "06/30"
    ),
    'Dates V2'[Date] <= TODAY()
)

What I came to realize is that when I put this in a table visual with the academic year’s column, it would only return the correct results for the current academic year, but historically, it fell apart.

So I modified to the below formula which works but I am wondering if there was an easier way of accomplishing this same thing.

Revised YTD Registrations = 
VAR CurrentDate = TODAY()
VAR CurrentYear = YEAR(CurrentDate)
VAR CurrentMonth = MONTH(CurrentDate)
VAR CurrentDay = DAY(CurrentDate)

-- Calculate the equivalent date in the current academic year
VAR EquivalentDate = 
    IF(
        CurrentMonth >= 7,
        DATE(CurrentYear, CurrentMonth, CurrentDay),
        DATE(CurrentYear - 1, CurrentMonth, CurrentDay)
    )

-- Calculate the equivalent date for each row in the Dates table
VAR AcademicYearStart = 
    IF(
        MONTH(MAX('Dates V2'[Date])) >= 7,
        YEAR(MAX('Dates V2'[Date])),
        YEAR(MAX('Dates V2'[Date])) - 1
    )
VAR EquivalentDateInAcademicYear = 
    DATE(
        AcademicYearStart + (IF(CurrentMonth >= 7, 0, 1)),
        CurrentMonth,
        CurrentDay
    )

RETURN
CALCULATE(
    [Registrations],
    FILTER(
        'Dates V2',
        'Dates V2'[Date] <= EquivalentDateInAcademicYear &&
        'Dates V2'[Date] >= DATE(AcademicYearStart, 7, 1) &&
        'Dates V2'[Date] <= DATE(AcademicYearStart + 1, 6, 30)
    )
)
2

James,

Can you share a minimal working example PBIX file? That would make it easier for others to contribute validated solutions.

If you add information about your academic calendar to your date table with a column like:

Academic Year = 
IF(
    MONTH('Dates V2'[Date]) >= 7,
    YEAR('Dates V2'[Date]),
    YEAR('Dates V2'[Date]) - 1
)

You can use that column in your measures and visuals. Adding Academic Year to the date table simplifies performing time intelligence calculations and ensures consistency and accuracy across different visuals and measures. This approach makes your measures simpler and easier to maintain.

For instance, with the Academic Year column in your table, you can use a simpler measure like:

Registrations YTD = 
CALCULATE(
    [Registrations],
    FILTER(
        ALL('Dates V2'),
        'Dates V2'[Academic Year] = MAX('Dates V2'[Academic Year]) &&
        'Dates V2'[Date] <= TODAY()
    )
)

If you can share a sample dataset, you’ll get a much better response from the community.

3

Using the EDNA Date Table you can start of the fiscal Year for July instead of a normal calendar year.

Just wondering why this won’t work.

Thanks
Keith

4

It could work. However, repurposing the fiscal year column in the date table might cause conflicts if you need to report on the budget year separately. It’s essential to keep the fiscal year and academic year data distinct if both are used for different reporting purposes.

5

Very true, I’m not sure how they do their financial results to the Government. If the financial reporting and the academic is the same then it should work.

Keith

6

James,

you can use the calendar table created by Melissa where you have the ability to create a fiscal year starting at 7 you can change the name in Academic Year if neccessary. below the link

kind regards
Roger

1 Like
7

@JamesMWilliams
Just to add here is the link within the forum where the extended date table @Melissa is located

thanks
Keith

8

We use both, example Academic Year (AY) is always written in YYYY-YY format so 2023-24 is the July 1, 2023 to June 30, 2024 academic year. Fiscal Year is essentially the same except its written as FYXX so for the Academic Year of 2023-24 the Fiscal Year would be FY24. We use FY when dealing with revenue and expenses and everything else is report as AY.

9

I will have to work on creating one that doesn’t use real data but close enough. That has been a project on my list for a year now.

10

When I worked in the education sector, our academic years ran from Aug-Jul and in the format of e.g. ‘23/24’, but I had an academic year column in my date table (built on the Enterprise DNA extended date table):

AddAY = Table.AddColumn( ReorderColumns, "Academic Year", each

if [MonthOfYear] >= 8
then Text.End( Text.From([Year]), 2) & “/” & Text.End( Text.From([Year] +1), 2)
else Text.End( Text.From([Year] -1), 2) & “/” & Text.End( Text.From([Year]), 2), type text)

I seem to recall Brian and/or Melissa helped me with this on here.