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!

Utilizing GUID’s for Importing Data to Multiple Orgs in Dynamics CRM

Do you have multiple entities in your Dynamics CRM system that are simply used for look-ups? Or maybe you want to replicate data across multiple orgs, but have workflows that specify specific records using lookups? This can be a hindrance when you have to move these workflows from one CRM organization to another, because the look-up value (label) may be the same from one CRM organization to another, but the GUID will be different because it was more than likely imported/entered manually.

Did you know that the "Out of the Box" Data Import Wizard can help you keep the GUID (Primary Keys) Synchronized over multiple CRM organizations so you don't have to keep updating workflow look-ups each time you move them from Development > QA > Production?

The process is pretty simple:

  1. Determine which organization will be the Master (Usually the development Organization works best).
  2. Enter or Import all values for the Entity.
  3. Export the Entity using the Export Tool for CRM (Don't forget to check the box "Make this data available for re-importing by including required column headings)

    Utilizing GUID’s for Importing Data to Multiple Orgs in Dynamics CRM

  4. Save the file to your Desktop (Or somewhere you will remember).
  5. Open the Import Wizard in your target organization.

    Utilizing GUID’s for Importing Data to Multiple Orgs in Dynamics CRM

  6. Select your File.
  7. Choose your Data Map – (Default (Automatic Mapping) works slick if you don't have one already).

    Utilizing GUID’s for Importing Data to Multiple Orgs in Dynamics CRM

  8. When setting up your field mapping, simply add the field containing the GUID (ONLY exported if you remember to check that box on the export of data!) to the Primary Key field of your target entity.

    Utilizing GUID’s for Importing Data to Multiple Orgs in Dynamics CRM

  9. Click Next.
  10. And Next again to get past the Review Mapping Summary screen.

    Utilizing GUID’s for Importing Data to Multiple Orgs in Dynamics CRM

  11. Hit Submit.

    Utilizing GUID’s for Importing Data to Multiple Orgs in Dynamics CRM

And there you have it...synchronization  is now complete! If you have any more data import questions make sure to check out our blog focused wholly on Dynamics CRM or stay up to date via our CRM Book. Happy CRM'ing!