There are many different articles available on Time
Intelligence in Power BI. Many new users
to Power BI do not really understand the importance of having a date table in
their data model. I have spoken on this
topic on many different occasions and continue to receive a lot of questions
about the topic. In this article I am
going to try and cover a few tips on how to work with dates in your Power BI
model.
Many new users simply use the Auto Date & Time function
of Power BI. This function automatically
creates a hidden date table for each field in the model with a date or
date/time data type. This function is
enabled by default and it allows for slicing by Year, Quarter, Month and
Day. The problem with Auto Date and Time
is that it only works with a standard calendar (Jan to Dec). It requires a datetime column in the fact
table. It does not work with multiple
fact tables and it does not support time intelligence calculations (YTD, MTD,
etc.). So there must be a better way.
This better way is to create a date table in the data
model. A date table can be used for time
intelligence calculations. However the
table must contain a row for every date in the date range of the data and it
often includes additional columns like week number, workday, holiday, etc.
A date table can easily be created with both DAX and M. There are many different blogs available with
the basics of creating a date table (Radacad or Excelerator BI) One thing I find many
of these articles missing is directions on how to handle a fiscal year, and
every time I speak about creating date tables I have several attendees ask
about working with their fiscal year.
Now a fiscal year can be a very complicated topic because it
may include a 4-5-4 calendar or some variation where the beginning date and
ending date are not the same. While this
can be handled in Power BI, it typically requires some unique formulas to
address the specifics of the company. In
this example, I will create a date table for a company that operates on a July
to June fiscal year.
In order to create the fiscal year columns, I have added a
variable to both the DAX date table script and the M date table script. The variable FiscalStart is used to set the
first month of the fiscal year. In my
example this would be 7 or 07.
Then use following logic in DAX or M to create the fiscal
year columns.
• Fiscal
Year = if (First Fiscal Month <= Month) then (Year +1) else (Year)
• Fiscal
Month # = if(First Fiscal Month <= Month) then (Month – First Fiscal Month +
1) else ((12 – First Fiscal Month + 1)+Month)
Here is some sample code Using DAX
• "Fiscal
Year", IF(FiscalStart <= FORMAT([Date],"MM"),
"FY"&(YEAR([Date])+1),"FY"&YEAR([Date]))
• "Fiscal
Month", Value(IF(FiscalStart <= FORMAT([Date],"MM"),
(Month([Date]) -FiscalStart+1), ((12-FiscalStart+1)+Month([Date]))))
• "Fiscal
Quarter", If (Value(IF(FiscalStart <= FORMAT([Date],"MM"),
(Month([Date]) -FiscalStart+1), ((12-FiscalStart+1)+Month([Date])))) <
4,"Q1", If (Value(IF(FiscalStart <= FORMAT([Date],"MM"),
(Month([Date]) -FiscalStart+1), ((12-FiscalStart+1)+Month([Date])))) <
7,"Q2", If (Value(IF(FiscalStart <= FORMAT([Date],"MM"),
(Month([Date]) -FiscalStart+1), ((12-FiscalStart+1)+Month([Date])))) <
10,"Q3","Q4")))
Here is some sample code using M
• "Fiscal Month", each if(FiscalStart
<= [Month]) then [Month] - FiscalStart +1 else (12 -
FiscalStart+1)+[Month]),
• "Full
Fiscal Month", each if [Fiscal Month]< 10 then
Text.Combine({"0", Text.From([Fiscal Month], "en-US")})
else Text.From([Fiscal Month],"en-US")),
• "Fiscal
Year", each if (FiscalStart <= [Month]) then [Year]+1 else [Year]),
• "Day
of Fiscal Year", each Duration.Days([FullDateAlternateKey]-[Beginning
Fiscal Year])),
• "Week
of Fiscal Year", each Number.Round([Day of Fiscal Year]/7,0,0)),
• "Quarter
of Fiscal Year", each Number.Round(([Fiscal Month]+1)/3,0,0)),
I have written two basic script files to create a complete
date table with Fiscal Year columns. Feel
free to download them and give them a try.
To use, Open Power BI, go to Modeling Tab, select New Table
– copy the entire script into the formula bar at the top of the screen.
To use, Open Power BI, Get Data, Blank Query, From the Power
Query editor, choose Advanced Editor and paste the entire script into the
window. Once the script run you can
rename the Query to Date to make it easier to use in your data model.
Hopefully this gives you a better understanding on how to work with Fiscal Years in Power BI.
Comments
Post a Comment