sum values between two calendar dates

[Anonymous]

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 

[Eric_Zhang · Accepted solution]

---

@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]
)

[Greg_Deckler]

So, yes, generally you would have date table that contains all of the information about particular dates such as Month, Year, Fiscal Year, Fiscal Quarter, etc. Like:

Date,Day,Month,Quarter,Year,Fiscal Month,Fiscal Quarter,Fiscal Year

1/10/13,1,10,Q4,2013,1,1,2014

You relate this to your fact table by date and then you can easily sum values within particular fiscal years, etc.

[Anonymous]

Thank you so much for your help 🙂