Custom Calendar in Power BI (DAX) - Calculated column for Fiscal Quarter

Question

I am building a custom calendar in Power Bi.

I work mostly with US Fiscal Year values, which means that a Fiscal Year 2017 started on October 1st (2016-10-01) and will end on September 30th (2017-09-30).So, current calendar year compared to fiscal year would look as follows:

===============================================
Calendar YearMonth     ||   Fiscal YearMonth
===============================================
201601                 ||   201604
201602                 ||   201605
201603                 ||   201606
201604                 ||   201607
201605                 ||   201608
201606                 ||   201609
201607                 ||   201610
201608                 ||   201611
201609                 ||   201612
201610                 ||   201701
201611                 ||   201702
201612                 ||   201703

In order to calculate month-to-date, quarter-to-date and year-to-date values, I need a working calendar. The way I tried to approach the issue, is that I have created calculated columns.

I have managed to get Fiscal Year and Fiscal Month No. formulas to work:

Fiscal Year = IF(MONTH([DATE]) > 9, YEAR([DATE])+1, YEAR([DATE]))

Fiscal YearMonth No = 
SWITCH( 
    TRUE(),
    MONTH('Calendar'[Date]) = 10, 1,
    MONTH('Calendar'[Date]) = 11, 2,
    MONTH('Calendar'[Date]) = 12, 3,
    MONTH('Calendar'[Date]) = 1, 4,
    MONTH('Calendar'[Date]) = 2, 5,
    MONTH('Calendar'[Date]) = 3, 6,
    MONTH('Calendar'[Date]) = 4, 7,
    MONTH('Calendar'[Date]) = 5, 8,
    MONTH('Calendar'[Date]) = 6, 9,
    MONTH('Calendar'[Date]) = 7, 10,
    MONTH('Calendar'[Date]) = 8, 11,
    MONTH('Calendar'[Date]) = 9, 12
    )

But unfortunately I cannot seem to figure out a formula to get Fiscal Year Quarter No. My latest attempt was to use SWITCH function together with DATESBETWEEN as follows:

Fiscal YearQuarter No = 
SWITCH( 
    TRUE(),
    (DATESBETWEEN('Calendar',MONTH('Calendar'[Date]) >= 9, MONTH('Calendar'[Date])) <= 12)), 1,
    (DATESBETWEEN ('Calendar'[Date],MONTH('Calendar'[Date]) >= 1, MONTH('Calendar'[Date]) <= 3)), 2,
    (DATESBETWEEN ('Calendar'[Date],MONTH('Calendar'[Date]) >= 4, MONTH('Calendar'[Date]) <= 6)), 3,
    (DATESBETWEEN ('Calendar'[Date],MONTH('Calendar'[Date]) >= 7, MONTH('Calendar'[Date]) <= 9)), 4
)

Unfortunately I am getting a syntax error.

RedVII RedVII · Accepted Answer · 2016-12-20T17:44:33

Since the MONTH() function returns an integer value, I think that is why you are getting a syntax error by wrapping that with a DATESBETWEEN() function.

Instead, just use simple integer operators. I tested your data with this calculation and it works:

Fiscal YearQuarter No = 
SWITCH(
    TRUE(),
    MONTH('Calendar'[Date])>=10 && MONTH('Calendar'[Date]) <= 12,1,
    MONTH('Calendar'[Date])>=1 && MONTH('Calendar'[Date]) <= 3,2,
    MONTH('Calendar'[Date])>=4 && MONTH('Calendar'[Date]) <= 6,3,
    MONTH('Calendar'[Date])>=7 && MONTH('Calendar'[Date]) <= 9,4
)

Also, you probably do not need such a long expression for calculating your Fiscal month numbers, because your fiscal calendar is simply 3 months offset from the standard calendar. Try an arithmetic formula to replace it with. This should work:

Fiscal YearMonth No= = 
=IF((MONTH('Calendar'[Date])+3)>12,ABS(12-(MONTH('Calendar'[Date])+3)),(MONTH('Calendar'[Date])+3))

Custom Calendar in Power BI (DAX) - Calculated column for Fiscal Quarter

2 Answers