Running Totals over longer than a year

[GouldM]

Hi,

I've read a fair bit about date tables/Calendars and can see how to do running totals for a year or for a given sub-year period - Quarter/Month etc.

However, we have a need to accumulate over a different, longer time period. I work in education, and we receive applications for courses from the start of one academic year, through into the following year. For example Applications for courses Starting in 24/25 year will be received from the start of the 23/24 year through and into 24/25

I thought I would be able to create my own 'date hierarchy' so that each application period could contain dates over the two years. However, I don't see a way to create a CALCULATE column/measure that works correctly. 

I thought I could create an ApplicationCalendar table but dates are not unique in the table - For example 1st September 2024 exists in the 23/24 Application period and the 24/25 Application period.

[bhanu_gautam]

@GouldM , You can create a date table and add application period in it

ApplicationCalendar =

ADDCOLUMNS (

    CALENDAR (DATE(2023, 1, 1), DATE(2025, 12, 31)),

    "ApplicationPeriod",

    IF (

        MONTH([Date]) >= 9,

        YEAR([Date]) & "/" & YEAR([Date]) + 1,

        YEAR([Date]) - 1 & "/" & YEAR([Date])

    )

)

And for running total you can use DAX

RunningTotalApplications =

CALCULATE (

    SUM(Applications[ApplicationCount]),

    FILTER (

        ALL (ApplicationCalendar),

        ApplicationCalendar[ApplicationPeriod] = MAX(ApplicationCalendar[ApplicationPeriod]) &&

        ApplicationCalendar[Date] <= MAX(ApplicationCalendar[Date])

    )

)

[GouldM]

Hi this only gives one date in each period. I've included a screen shot of a chart from excel which demonstrates the general idea that I'm trying to do in Power BI. The issue for me is not that a year begins other than 1st Jan, but that I want to accumulate over a period longer than a year as here, but I need a time hierarchy on the X axis.