I came across Chris Webb’s excellent post (Creating Current Day, Week, Month And Year Reports In Power BI Using Bidirectional Cross-Filtering And M) in which he demonstrated how to leverage the Bi-Directional filtering in Power BI. Chris goes into great detail to explain how he goes about creating the functions, tables and how it all pieces together.

This got me thinking and I came up with a way to have both Fiscal or Calendar Date Slicers in Power BI. So in the steps below I will show you how I extended on what Chris Webb did, to make things easier. As well in a later blog post to leverage the Fiscal or Calendar dates for when displaying Month names on a chart visual.

I then had the opportunity to present at the Power BI User Group in Brisbane and below is what I presented to the people who attended.

Below is an example showing how it dynamically switches between the Fiscal and Calendar Months. The thing you will notice is how the Month Names change when changing from LY to LY Fiscal. Which dynamically switches between the Fiscal and Calendar Months.

NOTE: Please click on the GIF below if it is too small to see what is happening.

Part 1 – Starting with the default Calendar Dates from Chris Webb’s blog post

The first thing that I did was to download the workbook example from the blog post, which you can also download from here DynamicDateSelections.pbix

This is a quick overview in terms of what was detailed in Chris Webb’s blog post.

Creating the Function in Power Query

The first explanation is how Chris created the function in Power Query.

I start with the function first which is shown below. This is the function that requires the parameters below, and in turn creates the Period table.

As you can see above it requires the following parameters, Period Name, Start Date, End Date and Sort Order which are explained below:

• Period Name is for the Date range for the Period
  • EG: YTD (Year to Date)
• Start Date is for the start date for your Period (YTD)
  • EG: 01-01-2016
• End Date is for the end date for your Period (YTD)
  • EG: If today’s date is 06 Sep 2016 the End Date would be 06-09-2016
• Sort Order is how you want to sort our Periods

Creating the Period Table in Power Query

Next is the actual code that will generate the values to be passed through to the function. Which in turn will create the period table.

Below is a simple example of the Power Query Code “M” which has to examples for Today’s Date, and YTD

What is happening above is the following

• First it is getting Todays Date
  • With this example Todays Date will be 06 Sep 2016
• Next is where it is defining the range of dates
  • This is where you can define all of your periods.
  • As with our example I have only shown 2 above.
• Then within the range is where I am putting in the required parameters for the function above as explained below using the YTD example
  • 
  • The function first Parameter called
    Period Name
    • So with our YTD this is shown above with
    • 
  • Next the second
    Parameter is called Start
    Date which is where I get the first date of the year, using the inbuilt Power Query formula
    • 
    • In here we start from the inside out.
      • So the first part is getting todays date 06-09-2016
      • Next using the Power Query formula Date.StartOfYear is saying get the First Date of the Year
        • 01-01-2016
      • And then finally convert this to a date by using the Power Query formula Date.From
    • So once this is done, it will give the Start Date of 01-01-2016
    • And every time you refresh this, it will check your current date and adjust accordingly.
  • The third parameter is called End Date, which is where I get todays Date
    • 
    • So this will be 06-09-2016
  • And the final parameter is the Sort Order, which is the order that it will be shown.
    • 
    • So this is saying it will be shown second.
• Then it then using the Power Query Formula to get a list from the range and pass it to the CreatePeriodTable function, and output that into the GetTables
  • 
  • The refers to the parameter values from within the range.
• And then finally it outputs the data and combines it all into one table.
  • 

So once the above is done this is what table looked like:

NOTE: Relationship between Period and Date Table

I just wanted to make a quick note, that as per Chris’s blog post there is already the relationship defined between the Period and Date table as shown below:

Part 2 – Extending the Function & Period Table to cater for Fiscal or Calendar Dates

In this next section below I am going to explain how I extended both the function and the Period table to cater for if it is a fiscal or calendar date.

Below is what the final output looks like in the Period Table

Extending the Function

What I did was to extend the function to accept one more parameter which is the Period Type.

So below is the actual code that I modified in the Advanced Editor in Power Query

(

PeriodName as text,

StartDate as date,

EndDate as date,

SortOrder as number,

PeriodType as text

) as table =>

let

DayCount = Duration.Days(EndDate-StartDate)+1,

DateList = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),

AddPeriodName = List.Transform(DateList,

each {PeriodName,_,SortOrder,PeriodType}),

CreateTable = #table(

type table[Period=text, Date=date, Sort=number,PeriodType=text],

AddPeriodName)

in

CreateTable

As you can see above I added the Period Type as required, it is highlighted in RED above.

So when complete and looking at it in Power Query it now has the additional parameter

Extending the Period Table

Now in order to extend the period table I needed to add in the additional parameter as shown below:

let

TodaysDate = Date.From(“06-09-2016”),

Ranges = {

{“Today”,

TodaysDate,

TodaysDate,

1,

“Calendar”},

{“YTD”,

Date.From(Date.StartOfYear(TodaysDate)),

TodaysDate,

2,

“Calendar”}

},

GetTables = List.Transform(Ranges,

each CreatePeriodTable(_{0}, _{1}, _{2}, _{3},_{4})),

Output = Table.Combine(GetTables)

in

Output

• So in order to make the required changes I did the following.
  • I added in the additional field after the sort order “Calendar”, highlighted in RED
• And then when passing the values to the CreatePeriodFunction I had to specify the additional parameter value, highlighted in ORANGE

So now when you see the Period table it looks like the following:

Part 3 – Adding in Parameters for Fiscal Start and End Months

In this section what I did to make it easier was to add in parameters for the start and end fiscal months.

As well as modify the underlying Power Query M code, to take this into account.

Creating the Parameters

I created the following 2 Parameters as shown below:

Modifying Power Query M Code for Parameters

Next is where I added the following into the Power Query M code, so that it would be dynamic in terms of always using the correct Fiscal start and end months.

Below is the code and an explanation of what I did.

The reason for doing it this way, is that I have now defined it once and can re-use it anywhere else within my Power Query M Code.

let

TodaysDate = Date.From(DateTimeZone.FixedUtcNow()),

YearText = Number.ToText(2000),

FiscalStartMonth = Date.Month(Date.FromText(“”&YearText&”-“&#”Fiscal Start Month”&“”)),

FiscalStartMonthDay = Date.Day(Date.StartOfMonth(Date.FromText(“”&YearText&”-“&#”Fiscal Start Month”&“”))),

FiscalEndMonth = Date.Month(Date.FromText(“”&YearText&”-“&#”Fiscal End Month”&“”)),

FiscalEndMonthDay = Date.Day(Date.EndOfMonth(Date.FromText(“”&YearText&”-“&#”Fiscal End Month”&“”))),

• So what I did was to first create YearText
  • As you can see I used the Year 2000
  • The reason is that this is only used to actually get the Date formatted in the Fiscal requirements below
  • EG: In order to get a Month Number you need to have a valid date.
    • So if we have a date of 2000-07, we are only ever looking for the 07
• Next I went and created the following
  • FiscalStartMonth – This is the Fiscal Month Start Number
    • EG: I wanted it to give me an output of 11
    • You will also see that I put in the Parameter highlighted in RED
      for the Fiscal Start Month
  • FiscalStartMonthDay – This is the first day of the Fiscal Month
    • EG: I wanted it to give me an output of 01
    • You will also see that I put in the Parameter highlighted in RED
      for the Fiscal Start Month
  • FiscalEndMonth – This is the Fiscal Month End Number
    • EG: I wanted it to give me an output of 10
    • You will also see that I put in the Parameter highlighted in ORANGE
      for the Fiscal End Month
  • FiscalEndMonthDay – This is the last day of the Fiscal Month
    • EG: I wanted it to give me an output of 31
    • You will also see that I put in the Parameter highlighted in ORANGE
      for the Fiscal End Month

Using the Values within my Period formula for Fiscal Dates

In this section below I will explain how I then created periods for Fiscal Dates.

The example below that I am going to show is for Fiscal Year – Last Year (FY-LY)

{“FY-LY”,

if Date.Month(TodaysDate) < FiscalStartMonth then #date(Date.Year(Date.AddYears(TodaysDate,-2)),FiscalStartMonth ,FiscalStartMonthDay) else #date(Date.Year(Date.AddYears(TodaysDate,-1)),FiscalStartMonth ,FiscalStartMonthDay),

if Date.Month(TodaysDate) < FiscalEndMonth then #date(Date.Year(Date.AddYears(TodaysDate,-1)),FiscalEndMonth ,FiscalEndMonthDay) else #date(Date.Year(Date.AddYears(TodaysDate,+0)),FiscalEndMonth ,FiscalEndMonthDay),

3,

“Fiscal”},

• The first line is our Period Name “FY-LY”
• Next is where I get the starting Period for our Fiscal Year for Last Year (01-07-2015)
  • As you can see above I have put in an if statement, in which I can validating todays Month Number, and if it is less than the FiscalStartMonth Then go back
    2 years, else go back 1 year
    • So for example if our Fiscal Start Month is July, the number will be 07.
    • So if today’s date is 06-09-2016, that means the Month number
      currently is 09
    • So based on the above, this is NOT
      true, so only go back One Year.
    • NOTE: You can see that because I am defining the #Date, I have to use the required format (YEAR,MONTH NUMBER,DAY) EG: #Date(2015,07,01)
• Now I did the same for the Ending Period, but this time I changed it to use the FiscalEndMonth
  • NOTE: You can see that because I am defining the #Date, I have to use the required format (YEAR,MONTH NUMBER,DAY) EG: #Date(2016,06,31)
• And then the final part is where I put the Period Type as “Fiscal” which will be explain further down below.
• This ensures that I now get the correct Period Range for Fiscal Year Last Year.

Adding additional Period into the Period table.

In the steps below I am going to add the following period “Calendar Year – Last Year – Previous Quarter” (CY-LY-PQ)

What this means is that if the current date is 06-09-2016 then the range for CY-LY-PQ would be: April 2015 – June 2015

,

{“CY-LY-PQ”,

Date.AddYears(Date.AddQuarters(Date.StartOfQuarter(TodaysDate),-1),-1),

Date.AddYears(Date.AddQuarters(Date.EndOfQuarter(TodaysDate),-1),-1),

5,

“Calendar”}

• What we are doing above is to get our Date ranges that we needed.

So now you can then load this table and close down the Power Query Editor, which will then load the data into your model.

And the reason for this is so that when you select an item from the Period Table it will flow via the Date table to the fact table.

Part 4 – Adding the Period Slicer and how it works

In the steps below I will show how to add the Period slicer and when selected how it changes the data dynamically.

• Navigate to the Period table and then select the Period on the report canvas as shown below.
  • 
• Then under the Visualizations change it to the Slicer
  • 
• Then go into the Format and under General, change the Orientation to Horizontal
  • 
• And then I formatted it under Items as shown below.
  • 
• And then finally I turned the Header Off and gave it the following Title
  • 
• So that once complete it looks like the following below.
  • 
• Now when you select a Slicer your values will change dynamically.

NOTE: The thing to NOTE is that when you change between Fiscal and Calendar Periods the Months are not sorting correctly.

Part 5 – Adding Dynamic Months based on Fiscal or Calendar Periods

So in this step we are going to be creating a new calculated column so that if the period selected is Fiscal based, then it must use the Fiscal Month Names. And likewise if the period selected is Calendar based then use the Calendar Month Name.

We will also be adding the Month Sort Order so that it will be sorted correctly.

Creating the Month Name calculated Column

The first calculated column to add is the Month Name.

• So what I did was to create a calculated column with the following DAX syntax, with an explanation afterwards.

  Month Name =
  

  IF (
  

  [PeriodType] = “Calendar”,
  

  RELATED ( ‘Date'[MonthName] ) & ” “,
  

  RELATED ( ‘Date'[Fiscal Month] )
  

  )
  

  • I am starting my DAX Calculated Measure with an IF statement highlighted in GREEN
    • The reason for using the IF statement is so that I can use it to decide if a value is True what to do, and if false then what to do.
  • Next is where I am specifying what to do if the Period Type = “Calendar” highlighted in LIGHT BLUE
    • And in this syntax I am getting the related Date Month Name .
    • This is so that my Month Name is unique across the dataset.
    • You will notice that there is a space at the end &” “
      • This is so that the concatenation between the calendar and fiscal month names are different.
      • This is to ensure later when we do the month sort order, because they are unique they will sort correctly.
    • NOTE: The reason for this is because in the next steps is where I want to sort the Month Names and in order to this I need a unique Source Column in order to do this.
  • Next is where I am specifying the FALSE portion of the IF Statement highlighted in ORANGE
    • NOTE: I do not need to next any more IF statements because my data has either Fiscal or Calendar.
    • And in this syntax I am getting the related Date Fiscal Month Name.
    • NOTE: The reason for this is because in the next steps is where I want to sort the Month Names and in order to this I need a unique Source Column in order to do this.
• Now once that is done I could then see my Month Name
  column.

Creating the SortMonthNumber calculated Column

• Now the critical part in order to show the correct dates is to have it in the correct sort order.
  • So with our example we want Calendar to be from Jan – Dec
  • And we want Fiscal to be from Jul – Jun
• So I created the following calculated column with the following DAX syntax, with an explanation afterwards.

  SortMonthNumber =

  IF (
  

  [PeriodType] = “Calendar”,
  

  RELATED ( ‘Date'[Calendar Month Number] ),
  

  RELATED ( ‘Date'[Fiscal Month Number] )
  

  )
  

  • I am starting my DAX Calculated Measure with an IF statement highlighted in GREEN
    • The reason for using the IF statement is so that I can use it to decide if a value is True what to do, and if false then what to do.
  • Next is where I am specifying what to do if the Period Type = “Calendar” highlighted in LIGHT BLUE
    • And here is where I am getting the related Date Calendar Month Number.
  • Next is where I am specifying the FALSE portion of the IF Statement highlighted in ORANGE
    • NOTE: I do not need to next any more IF statements because my data has either Fiscal or Calendar.
    • And in here I am getting the related
      Date
      Fiscal Month Number.
• This will then create my SortMonthNumber
  calculated
  column.
• Next I ensured that the formatting was set to Whole Number as shown below.
  • 
• And the final step was to now use this column for the Sort by Column for our Month Name
  calculated
  column we created above by configuring it as shown below:
  • 
• Now all that I did was to change the Month used in my visual from the Date Table and the Month Name
  column to the Period Table and the Month Name column
  • 
• And then when I clicked on my Slicer I got the expected
  results, as shown at the start of the post.

Changing the Month on the Visual

Now the next step is to change the bar chart from the Date Month, to the Month Name in the Period table.

• To do this simply remove the Month from the visual.
• And then add the Month Number
  • 
• Now when you interact with the visual you will see it change correctly when selecting a Calendar or Fiscal Month.
• NOTE: Here you can now show that the Fiscal Months are sorting correctly as they should.

Part 6 – Creating Period Measures easily

The final part is to show how easy it is to create measures using the Period ranges.

NOTE: Ideally you should already have your Periods created in your Period table.

So below I am going to show how to create the following Calculated Measures below

• The first is to create a measure for Calendar Year – Last Year with the syntax below.

  Sales CY-LY =

  CALCULATE ( [Sales], Period[Period] = “CY-LY” )

  • As you can see what I did below was to use the Calculate and then filter it by the Period Name where it is “CY-LY”
• The second is to create a measure for Calendar Year – YTD with the syntax below.

  Sales CY-YTD =

  CALCULATE ( [Sales], Period[Period] = “CY-YTD” )

  • As you can see what I did below was to use the Calculate and then filter it by the Period Name where it is “CY-LY”
• Now I can use that as any calculated measure.

Now drag this into your report and show them how it works.

Create the following visual for this below:

NOTE: That is the Date Month

As you can see above we now have a comparison between Last Year (LY) and Year to Date (YTD)

You can download the example file here: World Wide Importers – Power BI – Completed.pbix

As well as if you want to view it on the Web here is the Publish To Web Version: Publish to Web | World Wide Importers – Power BI – Completed

If there are any comments or questions, please leave it below.