Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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] )
@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] )
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.
Thank you so much for your help 🙂
User | Count |
---|---|
128 | |
112 | |
99 | |
65 | |
62 |
User | Count |
---|---|
138 | |
116 | |
102 | |
70 | |
57 |