How to use Time Intelligence with DAX?

How to arrange my dates according to Fiscal Year Calendar?

Date =

GENERATE (
CALENDAR(DATE(2021,1,1),TODAY ()),

VAR currentDay = [Date]
VAR day = DAY( currentDay )
VAR dayname = FORMAT([Date],"dddd")
VAR Weekdaynum = WEEKDAY([Date],2)
VAR month = MONTH ( currentDay )
VAR year = YEAR ( currentDay )
VAR monthname = FORMAT([Date],"mmmm")
VAR week = IF((WEEKNUM([Date],2))<10,"Week 0" & WEEKNUM([Date],2),"Week " & WEEKNUM([Date],2))
VAR qtr = "Qtr " & QUARTER([Date])
VAR yearmonthnumber = YEAR([Date]) * 100 + MONTH([Date])
VAR yearquarternumber = YEAR([Date]) * 100 + QUARTER([Date])
VAR yearmonthdatenumber = YEAR([Date]) * 10000 + MONTH([Date])*100 + day ( [Date] )
VAR mmmyy = LEFT(monthname, 3) & " " & RIGHT(year, 2)
VAR qqyy = LEFT(qtr, 5) & " " & RIGHT(year, 2)

RETURN ROW (
"Day", day,
"Day_Name", dayname,
"Week_Day_Num", Weekdaynum,
"Month", month,
"Month_Name", monthname,
"Week" , week,
"Quarter" , qtr,
"Year", year,
"Year Month Number", yearmonthnumber,
"Year Quarter Number", yearquarternumber,
"Yearmthdt", Yearmonthdatenumber,
"MMMYY",mmmyy,
"QQYY",qqyy

)

)
  1. Create a new table in Dax
  2. Copy and paste the above paste

Optional – Change the start date as needed

Automation – The Today() function will automatically the code at the latest date, i.e, today.

  • In the formula above, we took advantage of VAR to create measures within the measure and return the measures we specify.
  • In the CALENDAR date function we have specified 2021-1-1 as the start date and the end date of the current month using the EOMONTH function which brings the end date of a specified month, we have used the TODAY() function to specific the current day in the EOMONTH function.
  • Instead of using end of the month, we can also put current day’s date as the end date or we can also add extra days by just adding the number of days we want, which would make the calendar function’s specified values look like this
CALENDAR(DATE(2021,1,1),TODAY()+30)
  • After using the CALENDAR function, we have taken advantage of FORMAT, MONTH, YEAR etc. functions to create a date table that contains almost everything one would require in a Date table.

How to create Financial Date Calendar columns

In this guide, we will create financial year columns based on India’s financial year which starts on April and ends in March. If we already have a date table, we can create financial year columns using the formulas stated below.

  • First, we create a new column within our date table, then we use the IF formula to specify that if the month in our date table is less than or equal to the last month of the financial year (March in this case) then keep the year as it is, else add +1 to the year.
Financial Year = IF(Dates[Month] <= 3, Dates[Year] , Dates[Year] +1)
  • Now we will create a new month column based on our existing month column, we need this column to sort the months properly based on financial year.
Financial Month = Dates[Month_Name]
  • Next, we will create a column that will sort the Financial Month properly, we will specify that if the month is less than or equal to the end month of financial year then add the number of remaining months in the actual year (not financial year), in this case our financial end month was 3 so we added +9 to compete a whole year (12 months), similarly we have specified that we that if it’s not less than or equal to the end month of financial year then subtract it by the number of end month of financial because 9+3 makes 12 which is a whole year.

    To understand this formula better, if our FY started on July we would use +6 and -6 because those 6+6 makes a whole year.
FY Month Number =

    IF (

        'Dates'[Month] <= 3,

        'Dates'[Month] + 9,

        'Dates'[Month] - 3

    )
  •  Now that we have created the main columns we need for FY calendar, we can create other columns based on our FY columns using the formulas below.
FY MMMYY = LEFT(Dates[Financial Month], 3) & " " & RIGHT(Dates[Financial Year], 2)

FY YM number = (Dates[Financial Year] * 100) + Dates[FY Month Number]

FY = (Dates[Financial Year] - 1) & "-" & Dates[Financial Year]

FY-Y MMMYY = LEFT(Dates[Financial Month], 3) & " " & MID(Dates[FY], 3,3) & RIGHT(Dates[FY], 2)
Scroll to Top