Different time cycles - Fiscal Year (Jan–Dec) and Academic Year (Aug–Jul)

[cadlamp]

Hello!

I’m working on a Power BI report that needs to show data for two different year definitions because the budget cycle and academic cycle differ:

• Budget Year: January 1 – December 31
• Ending Academic Year: August 1 – July 31
• Starting Academic Year: August 1 – July 31

I have two fact tables:

• Budget Allocation - includes Budget Year
• Payment - includes Payment Date, Amount, and Tutoring Recipient
• Tutoring Recipient - includes recipient details

My goal:

• On the same report, display progress on the overall budget (Fiscal Year) and progress on individual student budgets (Academic Year, both the starting & ending).
• The page already has a slicer that filters on “Budget Year” (from Budget Allocation), but I’m open to other approaches

Is there a way to do this on the same page? If anyone has examples, best practices, or links to tutorials, I’d appreciate it!

Thanks in advance!

[AshokKunwar]

Hii @cadlamp 

If this solution worked for you, please Mark as Solution! It helps others in the community find this answer more easily. Cheers!

[ryan_mayu]

@cadlamp 

You can create a date table. 

For the first budge year, since the fiscal year is the same as calendar year. Then you just link the budge date to the date table.

for the next two tables, you can create a new date colum

newdate = EDATE('Ending Academic Year'[Date],-5)

then you use the new date column to link to the date table.

If this does not work, pls provide some sample data and expected output.

[DanieleUgoCopp]

Hello,

The key idea is to stop thinking in terms of two calendars and instead have one proper Date table that understands both logics. From the same date you can derive a budget year that runs January to December and an academic year that runs August to July. Once those year definitions live in the Date table, everything becomes much easier.

Your payments connect to the Date table through the payment date. Budget allocation can either use the budget year column from that same Date table or stay as it is if it’s already clean. When you write measures, the budget progress simply respects the budget year filter coming from the slicer, while the student-level measures deliberately use the academic year columns from the Date table. In DAX this is normal and safe, and visuals on the same page can happily follow different year definitions.

[AshokKunwar]

Hii @cadlamp 

Using a slicer from the Budget Allocation table limits your entire report to the Fiscal logic. Because the Academic Year (Aug–Jul) "straddles" two Fiscal years, a standard year filter will cut off your academic data prematurely.

​The Solution: The "Master Date" Approach

​Step 1: Create a Custom Calendar Table

​Do not use the date columns from your fact tables for slicers. Create a central Date table using DAX that defines both cycles:

DateTable = 
ADDCOLUMNS (
    CALENDARAUTO(),
    "Fiscal Year", YEAR([Date]),
    "Academic Year", IF(MONTH([Date]) >= 8, YEAR([Date]) & "-" & YEAR([Date]) + 1, YEAR([Date]) - 1 & "-" & YEAR([Date])),
    "Month No", MONTH([Date])
)

Step 2: Establish Relationships

​Connect your new DateTable[Date] to:

1. ​Budget Allocation[Date]
2. ​Payment[Payment Date]

​Step 3: Create "Snapshot" Measures

​Since your page is filtered by "Budget Year" (Fiscal), your Academic measures need to "ignore" the Fiscal filter to show the full Aug–Jul range. Use CALCULATE with ALL or REMOVEFILTERS.

​Budget Progress (Fiscal):

Budget Progress = SUM('Budget Allocation'[Amount])

Student Academic Progress:

This measure forces the calculation to look at the Academic cycle regardless of the Fiscal slicer:

Academic Year Payment = 
VAR CurrentAcademicYear = SELECTEDVALUE('DateTable'[Academic Year])
RETURN
CALCULATE(
    SUM('Payment'[Amount]),
    REMOVEFILTERS('DateTable'[Fiscal Year]),
    'DateTable'[Academic Year] = CurrentAcademicYear
)

Practice Implementation

1. ​The "Switch" Strategy: If you want one chart to toggle between Fiscal and Academic views, create a Field Parameter. This allows the user to change the X-axis from "Fiscal Year" to "Academic Year" dynamically.
2. ​Visual Separation: Use different background colors or headers for the "Fiscal" visuals and "Academic" visuals. This prevents user confusion when numbers don't seem to "sum up" identically due to the different date boundaries.
3. ​Syncing Slicers: If you keep the Fiscal slicer, add a "Warning" text box that appears (using conditional visibility) when an Academic visual is being truncated by a Fiscal selection.

Summary for the Community

​The key is to disconnect the slicer from the Fact table. Use a central Calendar table with an Academic Year column and use CALCULATE(..., REMOVEFILTERS('Date'[Fiscal Year])) to ensure your Academic totals remain accurate even when a Fiscal filter is active.

​If this dual-calendar approach solves your budget tracking issue, please mark this as the "Accepted Solution"!