Spreadsheets

Create a date table for your expense data model with DAX

Build a date table for your expense data model with DAX CALENDAR, add Year, Month and April to March Financial Year columns, mark it and link it to dates.

Editorial illustration for Create a date table for your expense data model with DAX

A date table is a separate table with one row per day, built either with the DAX function CALENDAR or from a list on a sheet, then given Year, Month and Financial Year columns, marked as a date table and linked to the transaction date. Without it, DAX time measures such as year to date will not behave, and an April to March year will not sort the way you expect.

How do I create the date list?

Where your tool supports calculated tables (Power BI Desktop does; check your own Excel version, because the Power Pivot window in Excel does not always offer them), create a new table with this expression:

  • Calendar = CALENDAR(DATE(2022,4,1), DATE(2026,3,31))
  • This returns one Date column from 1 April 2022 to 31 March 2026, which is four full April to March years or 1,461 days.
  • Alternatively, CALENDARAUTO(3) reads the dates already in your model and builds whole financial years ending in month 3, March. It looks at every date column, so a stray 1900 birth date in another table can stretch it badly.

If you cannot create calculated tables, make the list on a sheet instead: type 01-04-2022 in A2, enter =A2+1 in A3 and fill down to 31-03-2026, convert the range to a table named Calendar, and add it to the data model. From here the steps are the same.

Which columns should I add?

In the Power Pivot window, or in the model view of your tool, add these calculated columns to the Calendar table. Each one is a DAX formula.

ColumnDAX formulaExample for 15 Aug 2025
Year=YEAR([Date])2025
Month No=MONTH([Date])8
Month=FORMAT([Date],"MMM")Aug
FY Month No=MOD(MONTH([Date])-4,12)+15
Financial Year=IF(MONTH([Date])>=4, YEAR([Date]) & "-" & RIGHT(YEAR([Date])+1,2), (YEAR([Date])-1) & "-" & RIGHT(YEAR([Date]),2))2025-26

The FY Month No column is what makes April come first. Check it by hand: for January, MONTH is 1, so 1 minus 4 is minus 3. MOD of minus 3 by 12 is 9 in DAX, plus 1 gives 10, which is right because January is the tenth month counted from April.

How do I make Month sort in the right order?

A text column such as Aug sorts alphabetically, so April lands next to August. Select the Month column, use Sort by Column and pick FY Month No. Now pivots list April, May, June and so on through March.

  1. In the Power Pivot window, select the Calendar table and choose Design, then Mark as Date Table. Pick the Date column when asked. Excel checks that the column holds unique, unbroken dates.
  2. Open the relationships view and drag Calendar Date to the transaction Date column. The Calendar side is the one side and the transactions are the many side.
  3. Build a pivot with Financial Year and Month in Rows and a sum of Amount in Values, to test it.

Why not just use the dates in my transactions?

Because a transactions table has gaps. If you spent nothing on 3 March, that day does not exist in it, and year-to-date or previous-year measures that step through days will skip or misbehave. A calendar with every day gives them an unbroken line to walk along. It also gives you one place to add things like Month No or a Financial Year label, instead of formulas in every transaction row.

What should I check afterwards?

  • The first Date is the 1st of April and the last is the 31st of March, with no partial years.
  • Every transaction date falls inside the range. Count rows in a pivot against your transaction count.
  • The Financial Year column reads 2025-26 for a date in January 2026.
  • You extend the table when a new April starts.

If hand-building calendars sounds like more machinery than your expenses need, a tracker that already understands the April to March year can save the trouble; SpendixAI provides reports and a builder over imported records, though it does not run DAX.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports