Forum Discussion
Salutations-Fiscal Calendar in Power BI
I am struggling making a fiscal calendar. Mainly with generating the "Fiscal Month/Period" and how to get the month number. It was explained to me but I still cant grasp it. I wish coursera had actual live people to help us students, rather than a BOT. I am sorry if this is a burden. I am really trying.
Thank you Microsoft team,
Regards,
Jerz
Hi Jerzee
Different companies might have different ways of them deciding a financial year .
for the below example of calculated table you can create a fiscal calender staring from April
Date Table = ADDCOLUMNS( CALENDAR(DATE(2015,4,1), DATE(2030,3,31)), -- Fiscal Year "Fiscal Year", "FY" & FORMAT(YEAR([Date]) - IF(MONTH([Date]) < 4, 1, 0), "00"), -- Fiscal Year-Quarter "Fiscal Year-Quarter", "FY" & FORMAT(YEAR([Date]) - IF(MONTH([Date]) < 4, 1, 0), "00") & " " & "Q" & SWITCH( TRUE(), MONTH([Date]) IN {4,5,6}, 1, MONTH([Date]) IN {7,8,9}, 2, MONTH([Date]) IN {10,11,12}, 3, MONTH([Date]) IN {1,2,3}, 4 ), -- Month Name (Text) "Month", FORMAT([Date], "MMMM"), -- Actual Month Number (Calendar Month 1 = Jan) "Actual Month Number", MONTH([Date]), -- Fiscal Month Number (April = 1, May = 2, ..., March = 12) "Fiscal Month Number", MOD(MONTH([Date]) + 8, 12) + 1 )Hi Jerzee
Try the sampe DAX calculated table below
CalendarTable = VAR FinancialYearStartMonth = 7 -- set your financial year start month here (e.g., 7 = July) RETURN ADDCOLUMNS ( CALENDAR ( DATE ( 2015, 1, 1 ), DATE ( 2030, 12, 31 ) ), "Year", YEAR ( [Date] ), "Month", MONTH ( [Date] ), "Month Name", FORMAT ( [Date], "MMMM" ), "Year-Month", FORMAT ( [Date], "YYYY-MM" ), "Financial Year", YEAR ( DATE ( YEAR ( [Date] ) - IF ( MONTH ( [Date] ) < FinancialYearStartMonth, 1, 0 ), FinancialYearStartMonth, 1 ) ) + 1, "Financial Month", MOD ( MONTH ( [Date] ) - FinancialYearStartMonth + 12, 12 ) + 1 )
6 Replies
- lbendlin
Super User
You are solving the wrong problem. Calendars are immutable. There is no point in doing this in either Power Query or DAX. Use an external, precomputed reference table.
- kushanNa
Super User
Hi Jerzee
Different companies might have different ways of them deciding a financial year .
for the below example of calculated table you can create a fiscal calender staring from April
Date Table = ADDCOLUMNS( CALENDAR(DATE(2015,4,1), DATE(2030,3,31)), -- Fiscal Year "Fiscal Year", "FY" & FORMAT(YEAR([Date]) - IF(MONTH([Date]) < 4, 1, 0), "00"), -- Fiscal Year-Quarter "Fiscal Year-Quarter", "FY" & FORMAT(YEAR([Date]) - IF(MONTH([Date]) < 4, 1, 0), "00") & " " & "Q" & SWITCH( TRUE(), MONTH([Date]) IN {4,5,6}, 1, MONTH([Date]) IN {7,8,9}, 2, MONTH([Date]) IN {10,11,12}, 3, MONTH([Date]) IN {1,2,3}, 4 ), -- Month Name (Text) "Month", FORMAT([Date], "MMMM"), -- Actual Month Number (Calendar Month 1 = Jan) "Actual Month Number", MONTH([Date]), -- Fiscal Month Number (April = 1, May = 2, ..., March = 12) "Fiscal Month Number", MOD(MONTH([Date]) + 8, 12) + 1 ) - v-kathullac
Community Support
Thanks kushanNa lbendlin for Addressing the issue.
Hi Jerzee ,
we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.
Regards,Chaithanya.
- danextian
Super User
Hi Jerzee
Try the sampe DAX calculated table below
CalendarTable = VAR FinancialYearStartMonth = 7 -- set your financial year start month here (e.g., 7 = July) RETURN ADDCOLUMNS ( CALENDAR ( DATE ( 2015, 1, 1 ), DATE ( 2030, 12, 31 ) ), "Year", YEAR ( [Date] ), "Month", MONTH ( [Date] ), "Month Name", FORMAT ( [Date], "MMMM" ), "Year-Month", FORMAT ( [Date], "YYYY-MM" ), "Financial Year", YEAR ( DATE ( YEAR ( [Date] ) - IF ( MONTH ( [Date] ) < FinancialYearStartMonth, 1, 0 ), FinancialYearStartMonth, 1 ) ) + 1, "Financial Month", MOD ( MONTH ( [Date] ) - FinancialYearStartMonth + 12, 12 ) + 1 ) - v-kathullac
Community Support
Hi @Jerzee ,
we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.
Regards,Chaithanya.
- v-kathullac
Community Support
Hi @Jerzee ,
we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.
Regards,Chaithanya.