Sorting Data by Fiscal Year in Power BI

One of the questions we always ask when starting a new Power BI project is, "Does your organization follow the calendar year or a fiscal year?" It's critical to know because it determines the outcome of the reports for the organization!

The Power BI Desktop offers two functions to create a calendar table: Calendar and Autocalendar. These functions generate a single column, Date, in a new table. Typically, we then add other columns to the table, such as Year, Month, Quarter, Month Number, etc., in order to filter and sort data. Several options exist for creating the date/calendar table; in today's blog, we'll show how to use a DAX script to create a date/calendar table and then demonstrate how to achieve sorting by a fiscal year in slicer.

Let's use an example in which a company uses a fiscal year starting in April. The leadership team likes to see estimated sales in a column chart with a month slicer sorted by fiscal year, from April to March.

The steps to achieve the goal are as follows:

  1. Create a date/calendar table in DAX in Power BI Desktop
  2. Create a fiscal year number sort column in DAX
  3. Create a relationship between a fact table and the date/calendar table
  4. Create a column chart and a slicer with month name field
  5. Sort the month name with the fiscal year number field

*Assumption: the steps to connect to data source(s) and importing data to Power BI Desktop are completed in advance.

1. Create a date/calendar table in DAX in Power BI Desktop

A common practice is to run either a pre-defined DAX or M script to create a custom date/calendar table in Power BI Desktop. In our example, we use a DAX script.

It is important to note that we refrain from creating relationships between the date attributes in existing fact and/or dimensional tables in data sources to take advantages of the time intelligence functions and to optimize performance in Power BI.

Steps:

Select Modeling > New Table.

power bi

Date = ADDCOLUMNS(

CALENDAR("2015, 01, 01"," 2018, 12, 31"),

"Year", YEAR([Date]),

"Month Year Num", CONCATENATE(YEAR([Date]), FORMAT([Date],"MMM")),

"Month Num", Month([Date]),

"Month", FORMAT([Date], "MMM"),

"Quarter Num",FORMAT([Date], "Q"),

"Quarter", CONCATENATE("Q",FORMAT([Date], "Q")),

"Day", FORMAT([Date],"D"),

"Week", CONCATENATE("Week ",FORMAT([Date], "WW"))

)

*Note: The date range is set between 1/1/2015 and 12/31/2018 as an example

2. Create a fiscal year number sort column in DAX

Steps:

FYMonthNum =

          VAR FYStartMonth = 4

          //Update the fiscal year starting month above *Use number between 1 to 12

          RETURN

IF (

                    MONTH ( Dates[Date] ) >= FYStartMonth,

                    MONTH ( Dates[Date] )

- ( FYStartMonth - 1 ),

12

+ (

                         MONTH ( Dates[Date] )

- ( FYStartMonth - 1 )

)

)

A Sample Result:

power bi

If you have an M script, the following sample would help in adding a fiscal year number column in your date/calendar query:

AddFYMonthNum = Table.AddColumn(XXX, "FYMonthNum", each if
[MonthOfYear]>=FYStartMonth then [MonthOfYear]-(FYStartMonth-1) else 12+[MonthOfYear]-
(FYStartMonth-1))

Note: "FYStartMonth" is a variable to enter starting month of a fiscal year in function and XXX is a previous step name in M script

3. Create a relationship between a table and the date/calendar table

We connected Dynamics 365 Sales Online Free Trial as a data source and imported Accounts and Opportunities data. We created 1:N relationship between the Dates and opportunities tables with the Date and estimatedclosedate fields:

power bi

4. Create a column chart and a slicer with month name field

We created a sample report page with a year and a month slicer, and a column chart called, "Estimates by City."

power bi

5. Sort the month name with the fiscal year number field

As you can see above, the slicer lists Month in alphabetical order by default. We need to change the sort order to fiscal year with starting month of April.

Steps:

power bi

power bi

power bi

Now the month slicer sorts by fiscal year starting in April! Changing it to, say, July is as simple as amending the DAX script to say VAR FYStartMonth = 7. power bi

Pretty slick, right? We hope you find this useful.

To maximize your investment in Dynamics 365 and related solutions, our Power BI experts can transform your siloed data into stunning, interactive visualizations in a single view so you can make insight-driven decisions anytime, from anywhere. Learn more about our Power BI services here.

Happy Power BI'ing!

Changing “Named Based On” in Fiscal Year Settings

Here at PowerObjects, we are always looking for ways to make CRM easier to use, and in today's blog, we'll be showing you how to change the "Named Based On" in Fiscal Year settings so that you can get the results you are actually looking for when using Advanced Find. Let's get started!

The field labels in Fiscal Year Settings do not clearly indicate how to set up a Fiscal Year that doesn't coincide with the calendar year so that when you search by THIS Fiscal Year via an Advanced Find, you see records in the correct year. For example, say your FY2016 is 11/1/2015 – 10/31/2016. If you make no other change besides entering the Start Date, you will find that your date range is off by one year. Looking at this in an Advanced Find, we see that This Fiscal Year (FY2016) looks like it starts on 11/1/2016 instead of 11/1/2015.

Changing “Named Based On” in Fiscal Year Settings

Changing “Named Based On” in Fiscal Year Settings

This is such a common requirement though, so there has to be a way! And there is. For the following steps, we are assuming you have a security role with a level of permissions that lets you edit Fiscal Year Settings.

1. Navigate to the Business Management section via the Settings menu.

Changing “Named Based On” in Fiscal Year Settings

2. Click Fiscal Year Settings, and look at the Named Based On field. Change this from Start Date to End Date.

Changing “Named Based On” in Fiscal Year Settings

Now, when we search for dates within This Fiscal Year, we get results that start on 11/1/2015 instead of 11/1/2016. Mic drop . . .

Changing “Named Based On” in Fiscal Year Settings

Pretty nifty! That's all for the blog today. Make sure you check out PO TV, a brand new destination for video content from PowerObjects. Featuring video blogs, Customer Success Stories, CRM educational content, and much more, PO TV is your go-to destination for CRM video content.

Happy CRM'ing!