sum values between two calendar dates

---

Anonymous wrote:

Hi Everyone :) 

 

Background of problem, 

 

I have a calendar table, however it is not by the financial year I want to use, my companies financial year run from 1st October till the 30th of September the following year (e.g. 1/10/13 - 30/09/14 = FY14). I have managed to get my financial quarters correct by the year. However when I put this data in matrix table it splits up the year unless I specify with the quarter :(. Is it possible to create a new column that will have my financial year (1/10/13 - 30/09/14) as FY14 and the revenue values between this date band will sum, so I'm effectively summing between two dates? Is this possible? or is there a more straightfoward way to create a October to September calendar year? 

 

Thank you in advance and I hope my explanation above is not as convoluted as it sounds to me :/ and I hope someone is able to help. 

 

Kind regards, 

 

Pangea 

---

Anonymous

Check if you're looking for a calendar table as below?

calendar = 
ADDCOLUMNS (
    ADDCOLUMNS (
        CALENDAR ( "2012-10-01", "2022-09-30" ),
        "FiscalYear", IF ( MONTH ( [Date] ) < 10, YEAR ( [Date] ), YEAR ( [Date] ) + 1 ),
        "FiscalQuarter", SWITCH (
            TRUE (),
            MONTH ( [Date] ) >= 10
                && MONTH ( [Date] ) <= 12, "Q1",
            MONTH ( [Date] ) >= 1
                && MONTH ( [Date] ) <= 3, "Q2",
            MONTH ( [Date] ) >= 4
                && MONTH ( [Date] ) <= 6, "Q3",
            MONTH ( [Date] ) >= 7
                && MONTH ( [Date] ) <= 9, "Q4"
        )
    ),
    "FiscalYearQuarter", [FiscalYear] & "_"
        & [FiscalQuarter]
)