Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

sum values between two calendar dates

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 

1 ACCEPTED SOLUTION
Eric_Zhang
Employee
Employee


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

Capture.PNG

View solution in original post

3 REPLIES 3
Eric_Zhang
Employee
Employee


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

Capture.PNG

Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Thank you so much for your help 🙂 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.