Custom Calendar in Power BI (DAX) - Calculated column for Fiscal Quarter
<p>I am building a custom calendar in Power Bi. </p>

<p>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:</p>

<pre><code>===============================================
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
</code></pre>

<p>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.</p>

<p>I have managed to get  Fiscal Year and Fiscal Month No. formulas to work:</p>

<pre><code>Fiscal Year = IF(MONTH([DATE]) &gt; 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
    )
</code></pre>

<p>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:</p>

<pre><code>Fiscal YearQuarter No = 
SWITCH( 
    TRUE(),
    (DATESBETWEEN('Calendar',MONTH('Calendar'[Date]) &gt;= 9, MONTH('Calendar'[Date])) &lt;= 12)), 1,
    (DATESBETWEEN ('Calendar'[Date],MONTH('Calendar'[Date]) &gt;= 1, MONTH('Calendar'[Date]) &lt;= 3)), 2,
    (DATESBETWEEN ('Calendar'[Date],MONTH('Calendar'[Date]) &gt;= 4, MONTH('Calendar'[Date]) &lt;= 6)), 3,
    (DATESBETWEEN ('Calendar'[Date],MONTH('Calendar'[Date]) &gt;= 7, MONTH('Calendar'[Date]) &lt;= 9)), 4
)
</code></pre>

<p>Unfortunately I am getting a syntax error.</p>