BI-NSIGHT – Power BI (August Power BI Desktop Update, Future Download Report & Snap to Grid, IntelliSense coming to Power Query, Power BI Training, WebTuna Content Pack, Building a Real-Time IoT Dashboard, Chris Webb Loading Data from Multiple Excel Workbooks saved in OneDrive, SCCM Solution Template) – SQL Server (SSAS Integrated Workspace Mode, SSMS Update, Report Builder Update)

Last week there was not a lot on the go, but it seems to have picked up this week, so here are the latest insights.

Power BI – August Power BI Desktop Update

As I am sure some of you would have already seen and heard and I personally have to echo what other people have said, it is great to see how the Power BI team, as well as Microsoft take on board the issues and problems that their users face, and in this instance how quickly they have made the change, so that we can get not only the new functionality. But as requested also have the ability to use the older functionality also.

What I am referring to here is the Drill experience.

So you can use the double down arrow which will do the previous experience which will show the next level of the hierarchy.

And the new experience which is the split arrow which will perform the new inline hierarchy experience.

You can find the details here: August Power BI Desktop Update: Updated Drill experience

Power BI – Future state (Download Report) & Snap to Grid

As you can see with the image below on certain versions of the Power BI Service they have started provisioning the ability to download your Power BI report as a PBIX file. Which will be a great addition.

Next you can see that they have started working on the Snap to Grid functionality which is another great addition which will be available sometime in the near future.

Power BI – IntelliSense coming to Power Query

Just a quick update that IntelliSense is coming to Power Query, I have been working in the nuts and bolts of Power Query and this will be a very welcome addition.

Power BI – Training


There are some great free training available to get into Power BI, as you can see above there is a “Dashboard in an Hour” as well as “Dashboard in a Day”. Not only that but there is also a really great and valuable edX course (which I have already completed) which offers some great content and learning experiences.

You can find the details about the different options here: Power BI Dashboard in a Day and Dashboard in an Hour training near you

Power BI – WebTuna Content Pack

Here is another content pack, this time from WebTuna, which is an on-demand service for alerting and monitoring the performance of your internet or internet sits. And I am sure due to the nature of the business model as well as analytics in terms of usage and where it is coming from this will give some great insights into their customers data.

You can find all the details here: Explore your WebTuna Data with Power BI

Power BI – Building a Real-Time IoT Dashboard

In this blog post from the Microsoft Power BI team they show you how to setup, configure and create a real-time IoT dashboard.

Whilst the concept is very basic (which I think is a good thing to start learning) it also shows how powerful this can actually be.

You can find the details here: Building a Real-time IoT Dashboard with Power BI: A Step-by-Step Tutorial

Power BI – Tracking Changes in PBIX files

This is a blog post from Helen Gore, where they have created something that appears to be very simple, but actually is very powerful.

What she explains in her blog post is how they track changes in their Power BI Desktop (PBIX) files, so that when other people are working on the same file, they know what the previous changes were.

You can find all the details here: HOW TO TRACK CHANGES IN POWER BI DESKTOP

Power BI – Chris Webb – Loading Data from Multiple Excel Workbooks saved in OneDrive

This is an excellent blog post from Chris Webb where he shows how to not only load multiple Excel Workbooks, but ones that are saved on OneDrive Personal or OneDrive for Business, as well as ensuring that they get refreshed on your schedule.

I will not go into all the details, as you can read them on his blog post here: Loading Data From Multiple Excel Workbooks Into Power BI–And Making Sure Data Refresh Works After Publishing

Power BI – SCCM (System Center Configuration Manager) Solution Template

If my memory serves me correctly this is the second Power BI Solution Template from the Microsoft Power BI team.

And this time it is for SCCM, and having worked with this data in the past, it is really great to see how easily it can be used to show insights into your SCCM data. As well as it does appear that you can integrate 3rd party information in SCCM also.

You can find the details here: Announcing the Power BI solution template for System Center Configuration Manager

SQL Server – SSAS (SQL Server Analysis Services) Integrated Workspace Mode

This is a great update for SSDT and the modelling experience when working with SSAS Tabular models. You now can use an integrated workspace, which means you do not have to connect to an SSAS Tabular Server.

The thing to note is that you might need additional memory, as well as they highlight in the blog post drivers for both 32bit and 64bit depending on the version of SSDT.

You can find the details here: Introducing Integrated Workspace Mode for SQL Server Data Tools for Analysis Services Tabular Projects (SSDT Tabular)

SQL Server – SSMS (SQL Server Management Studio) Update

Here is the monthly update for SSMS, and there are quite a few updates in this release.

You can find all the details here: Download SQL Server Management Studio (SSMS)

SQL Server – Report Builder Update

It appears that for SQL Server 2016 the report builder will be following a similar release process as SSMS and SSDT where there will be more frequent updates to report builder.

I am sure that this will enable a lot of future updates and enhancements for report builder.

You can find the details here: SQL Server 2016 Report Builder update now available

Power BI – How to Easily Create Dynamic Date Table/Dimension with Fiscal Attributes using Power Query

I have been working on some new projects and one of the things that I found that was whilst you can find examples of creating Date tables, I could not find any examples of where the Date table included Fiscal Attributes such as Fiscal Month, Fiscal Quarter and Fiscal Year.

So in this blog post I am going to show how you can easily create your own date table with Fiscal Attributes in a few simple steps.

I have also created it in such a way that you can dynamically define your starting year, as well as your starting month for your fiscal attributes.

Below is a sample output of what I will explain below.

Creating Parameters

The first step is to create two parameters. These two parameters will be used to dynamically load our Start Year, as well as the Fiscal Start Month

Please NOTE you must create them with the same name and attributes. This is to ensure that the Power Query script will work correctly in the later steps.

This is how I created the Parameters.

  • You can go into Edit Queries, then click on Manage Parameters and I put in the following for Start Year
    • As you can see above I put in the Name “Start Year”
    • I then changed the Type to Decimal Number
    • And then put in my Current Value of 2014 for the year I want it to start from.
  • Next is the Parameters for Fiscal Start Month
    • As you can see above I put in the Name “Fiscal Start Month”
    • I then changed the Type to Decimal Number
    • And then put in my Current Value of 7
  • Once completed I had the following under Queries

Creating Date Table with Fiscal Attributes

In this next step is where I used the Power Query language “M” to create my Date Table with the Fiscal Attributes.

You are welcome to read through what I have done, but all that you will need to do is the steps below and copy and paste to get it working. Nothing else needs to be modified in the code below, due to the fact that I worked it all out dynamically using the parameters we created above.

For those people who are interested after the code I will highlight what I did to create the Fiscal Attributes.

Now in the Query Editor complete the following to create the Date Table

  • Click on New Source, then Blank Query
  • Next I gave the Query a name from Query1 to Date
  • Then in the Home Ribbon, click on Advanced Editor under the Query section
  • This will then open the Advanced Editor as shown below.
  • Now copy and paste in the code below.

     

    let
     Date = let
     Source = List.Dates,
     FiscalMonthCalc = 12-#"Fiscal Start Month",
     #"Invoked FunctionSource" = Source(#date(#"Start Year", 1, 1), Duration.Days(DateTime.Date(DateTime.FixedLocalNow()) - Date.AddDays(#date(#"Start Year",1,1),-1)), #duration(1, 0, 0, 0)),
     #"Table from List" = Table.FromList(#"Invoked FunctionSource", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
     #"Added Index" = Table.AddIndexColumn(#"Table from List", "Index", 1, 1),
     #"Renamed Columns" = Table.RenameColumns(#"Added Index",{{"Column1", "Date"}}),
     #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Year", each Date.Year([Date])),
     #"Added Custom1" = Table.AddColumn(#"Added Custom", "Month Number", each Date.Month([Date])),
     #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Day", each Date.Day([Date])),
     #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Day Name", each Date.ToText([Date],"ddd")),
     #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Month Name", each Date.ToText([Date],"MMM")),
     #"Reordered Columns" = Table.ReorderColumns(#"Added Custom4",{"Date", "Index", "Year", "Month Number", "Month Name", "Day", "Day Name"}),
     #"Added Custom5" = Table.AddColumn(#"Reordered Columns", "Quarter Number", each Date.QuarterOfYear([Date])),
     #"Duplicated Column" = Table.DuplicateColumn(#"Added Custom5", "Year", "Copy of Year"),
     #"Renamed Columns1" = Table.RenameColumns(#"Duplicated Column",{{"Copy of Year", "Short Year"}}),
     #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Short Year", type text}}),
     #"Split Column by Position" = Table.SplitColumn(#"Changed Type","Short Year",Splitter.SplitTextByRepeatedLengths(2),{"Short Year.1", "Short Year.2"}),
     #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Short Year.1", Int64.Type}, {"Short Year.2", Int64.Type}}),
     #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Short Year.1"}),
     #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns",{{"Short Year.2", "Short Year"}}),
     #"Added Custom6" = Table.AddColumn(#"Renamed Columns2", "Quarter Year", each Number.ToText([Short Year]) & "Q" & Number.ToText([Quarter Number],"00")),
     #"Reordered Columns1" = Table.ReorderColumns(#"Added Custom6",{"Index", "Date", "Day", "Day Name", "Month Number", "Month Name", "Quarter Number", "Quarter Year", "Short Year", "Year"}),
     #"Changed Type2" = Table.TransformColumnTypes(#"Reordered Columns1",{{"Date", type date}, {"Day", Int64.Type}, {"Index", Int64.Type}, {"Month Number", Int64.Type}, {"Quarter Number", Int64.Type}, {"Month Name", type text}, {"Quarter Year", type text}, {"Year", Int64.Type}}),
     #"Added Conditional Column" = Table.AddColumn(#"Changed Type2", "Fiscal Year", each if [Month Number] <= #"Fiscal Start Month" then [Year] else [Year]+1 ),
     #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Fiscal Month", each if [Month Number] <= #"Fiscal Start Month" then [Month Name] else [Month Name] ),
     #"Added Custom7" = Table.AddColumn(#"Added Conditional Column1", "Fiscal Month Sort Order", each Number.Mod(Date.Month([Date])+FiscalMonthCalc ,12)+1),
     #"Added Conditional Column3" = Table.AddColumn(#"Added Custom7", "Fiscal Quarter", each if [Fiscal Month Sort Order] >= 1 and [Fiscal Month Sort Order] <= 3 then "Q1"
     else if [Fiscal Month Sort Order] >= 4 and [Fiscal Month Sort Order] <= 6 then "Q2"
     else if [Fiscal Month Sort Order] >= 7 and [Fiscal Month Sort Order] <= 9 then "Q3"
     else if [Fiscal Month Sort Order] >= 10 and [Fiscal Month Sort Order] <= 12 then "Q4"
     else "Q Unknown" ),
     #"Added Conditional Column4" = Table.AddColumn(#"Added Conditional Column3", "Fiscal Quarter Sort Number", each if [Fiscal Month Sort Order] >= 1 and [Fiscal Month Sort Order] <= 3 then "1"
     else if [Fiscal Month Sort Order] >= 4 and [Fiscal Month Sort Order] <= 6 then "2"
     else if [Fiscal Month Sort Order] >= 7 and [Fiscal Month Sort Order] <= 9 then "3"
     else if [Fiscal Month Sort Order] >= 10 and [Fiscal Month Sort Order] <= 12 then "4"
     else "Q Unknown" ),
     #"Changed Type3" = Table.TransformColumnTypes(#"Added Conditional Column4",{{"Fiscal Quarter Sort Number", Int64.Type}, {"Fiscal Month Sort Order", Int64.Type}, {"Fiscal Year", Int64.Type}}),
     #"Renamed Columns3" = Table.RenameColumns(#"Changed Type3",{{"Date", "Calendar Date"}, {"Month Number", "Calendar Month Number"}, {"Month Name", "Calendar Month Name"}, {"Quarter Number", "Calendar Quarter Number"}, {"Quarter Year", "Calendar Quarter Year"}, {"Short Year", "Calendar Short Year"}, {"Year", "Calendar Year"}})
    
    in
     #"Renamed Columns3",
     #"Sorted Rows" = Table.Sort(Date,{{"Index", Order.Ascending}})
     in
     #"Sorted Rows"
  • Then click Done
  • Now you should see your Date Table with the Fiscal Attributes created.

Next I will show you how by changing the Fiscal Start Month how it dynamically changes the Fiscal Month Sort Order and Fiscal Quarter

  • As with our current example it is setup with the Fiscal Start Month of 7.
  • So the month of January will be the 7th Fiscal Month starting from July.
    • As well the Fiscal Quarter will be Q3.
  • Now I will change the Fiscal Start Month to 11
    • Click on the Fiscal Start Month and then change it from 7 to 11
    • As you can see below it currently is set to 7
    • And once done it is now changed to 11
  • Now when you go back to your Date table you will see the following changes.
    • For the Month of January, the Fiscal Month Sort Order will be 3
    • And the Fiscal Quarter will be Q1
  • You can try this for yourself and modify the parameters as required.

Technical overview on how I created the Fiscal Attributes

For the people who want to simply copy and paste and get it working you can skip to the conclusion, but for the people who want to understand how I did this please read below.

I had to come up with a way to get the right Fiscal Month Sort Order where the input could be dynamically driven. I did a fair bit of searching until I found someone who had done it in Excel, after which I modified it so that it would work with Power Query as well as bring back the right Fiscal Month Sort Order.

This is the code below, with the explanation afterwards.

  • The first part is where I created the FiscalMonthCalc

    FiscalMonthCalc = 12-#”Fiscal Start Month”,

    • What I am doing above is taking the total months in a year (12) and subtracting the Fiscal Start Month Parameter
    • This will be used in the code below so that we can get the right output.
  • Next is where I created the Fiscal Month Sort Order with the code below.

    Number.Mod(Date.Month([Date])+FiscalMonthCalc
    ,12)+1

    • As you can see above the first part highlighted in GREEN is where I am using the Mod (Divides two numbers and returns the remainder of the resulting number.)
    • In the next section is where I am getting the Month Number from the current Date row in the Date table highlighted in PURPLE
    • Now in order to get the number for our Mod, I then added this to my FiscalMonthCalc highlighted in RED
      • I had to do this in order to get the number for our Mod to be correct.
    • Then I next put in the divisor for our Mod which was 12 due to their being the 12 months of the year highlighted in ORANGE
    • The final part is where I had to increment the number by 1 to show the correct Fiscal Month Sort Order highlighted in BLUE

Then in order to create the Fiscal Quarter and Fiscal Quarter Sort Number I could then use the Fiscal Month Sort Order column created above.

This is the amazing thing with Power Query is that once I created my column I needed the next steps to create the Fiscal Quarter was as easy as defining our Quarters as we know which is every 3 months as shown below with the sample code.

#”Added Conditional Column3″ = Table.AddColumn(#”Added Custom7″, “Fiscal Quarter”, each if [Fiscal Month Sort Order] >= 1 and [Fiscal Month Sort Order] <= 3 then “Q1”

else if [Fiscal Month Sort Order] >= 4 and [Fiscal Month Sort Order] <= 6 then “Q2”

else if [Fiscal Month Sort Order] >= 7 and [Fiscal Month Sort Order] <= 9 then “Q3”

else if [Fiscal Month Sort Order] >= 10 and [Fiscal Month Sort Order] <= 12 then “Q4”

else “Q Unknown” ),

And because it is all driven off the Fiscal Month Sort Order it will always be correct due to being dynamically created.

Conclusion and download File

So in conclusion here is an easy way to create a Date table with Fiscal Periods that you can modify to your specific requirements.

Here is a link where you can download an example file: Creating Date Table with Fiscal Attributes.pbix

BI-NSIGHT – Power BI (Mobile Apps Update, Custom Visual Tool Development Update, Dynamic Security Cheat Sheet, JavaScript API, Infer Content Pack) – Microsoft Flow (Available to Everyone) – SQL Server Analysis Services 2016 (Tabular Editor)

Another great week in the world of BI.

Power BI – Mobile Apps Update for August 2016

Another month goes by and some more updates, this time from the Mobile Apps team.

In this update they have done quite a lot of work with regards to the ability to do manual refreshing of your data in iOS if you have a direct query.

The ability to use your favourite dashboard on all platforms, which is very handy when you have your selected items that you want to view, quite possibly from different data sets, but in one easy to view dashboard.

They have also made the data classification visible if you have enabled your data in the mobile app, so that the users are aware in terms of what the status is if the data they are looking at.

The new focus mode for iPad which makes it easier to view the data you want to very easily.

And finally the ability to create data driven alerts from within the Mobile App.

You can find all the details here: Power BI Mobile Apps feature summary – August 2016

Power BI – Custom Visuals Tool Development Update

I am not a custom visual developer myself but having recently worked with some developers it is great to see the updates and additions that they have added. I have no doubt that this will make the custom visual development a lot easier and extensible as we move forward.

You can find all the details here as to what has changed, updated and added: Custom visual developer tool now Generally Available

Power BI – Dynamic Security Cheat Sheet

Kasper De Jonge, has created another great blog post in which he shows how to create and use Dynamic security in Power BI.

He explains exactly how he did it, and even has a working example that you can download and try.

Here is the blog post with the details: Power BI Desktop Dynamic security cheat sheet

Power BI – JavaScript API for Power BI reports.

The guys from the Power BI team are a very busy team. And now they have released the Javascript API which will enable you not only to embed your Power BI report into an iFrame but also enable your page to interact with the Power BI reports.

You can find all the details here: Interact with Power BI reports using the JavaScript API

Power BI – Infer Content Pack

Here is another content pack and I am sure if you are existing Infer client then this will be a very welcome addition.

Not only that but in terms of what Infer does in providing predictive technologies, I am sure that over time the dashboards will become more valuable to your business.

You can find all the details here: Explore and Analyze your Infer data with Power BI

Microsoft Flow – Available to Everyone

I have played around with Microsoft Flow and it is a great product and really easy to use when you want to automate tasks, as well as possibly get data from a site, OData feed etc and store it in a location. This is very easily achievable with Microsoft Flow.

They now have made it Generally available to everyone, even if you do not have a work or school account.

You can find the details here: Microsoft Flow now available for everyone

SQL Server Analysis Services 2016 – Tabular Editor

For the people using SQL Server Analysis Services in compatibility mode 1200 there is a great blog post, as well as details about the application that Daniel Otykier has created.

It makes it a lot easier and simpler to create and use Tabular Models, now that it uses the new JSON format.

You can find all the details here on what he has done. It does look very promising indeed.

A new way to work with SQL Server Tabular Models: The Tabular Editor

Create Dynamic Periods for Fiscal or Calendar Dates in Power BI

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.

fiscal-calendar-change

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.

BI-NSIGHT – Power BI (Secure and Audit Power BI, Data Driven Parameters, Snowflake Data Connector) – Excel (Get & Transform Updates / Power Query Updates)

Here are the BI updates for this week.

Power BI – Secure and Audit Power BI

One of the questions that is regularly asked with regards to Power BI is around how secure the data is. And along with that is how do people gain access.

This new update goes a long way not only to make Power BI more secure. But also to have the ability to look at the audit logs and see who is doing what if required.

I have no doubt that this will go a long way in helping organizations secure their information, as well as be compliant from an audit perspective.

You can find the details here: Secure and Audit Power BI in Your Organization

Power BI – Data Driven Parameters

This is another great blog post by Chris Webb where he explains how to use the new updates in Power BI which enable you to now use Data Driven Parameters. So now you can dynamically have your requested parameters come from a list, which can be derived from an existing data set.

You can read up about it here: Data-Driven Power BI Desktop Parameters Using List Queries

Power BI – Snowflake Data Connector

In this blog post from the Microsoft Power BI team they explain what the Snowflake data connector is and how to connect and use it.

I am sure that it will be very welcome for the existing and potential Snowflake customers.

You can read up about it here: Power BI enables connectivity to Snowflake

Excel – Get & Transform Updates / Power Query Updates

IT is good to see that the Excel team has also found a way to keep on adding features and updates to the Get & transform or Power Query within Excel. I often find that they get released first to Power BI Desktop, but then they soon make it into Excel also.

This month there is a whole host of updates as shown below.

As you can see from above there are additional data connectors, as well as changes in the query editor. As well as finally making it easier to extract Date and Time functions from your existing columns. Which in the past can be rather tricky?

You can find all the details here: August 2016 updates for Get & Transform in Excel 2016 and the Power Query add-in

BI-NSIGHT – Power BI (Desktop Update for August, R Showcase, Content Pack Certification Program Overview, Power BI Publisher for Excel Update) – OkViz (Sparkline Visual)

Here is another week of BI news and information.

Power BI – Desktop Update for August

This month there is another great list of updates in Power BI Desktop. As I have done previously I will highlight what I personally think are great updates.

Report View Updates

There have been quite a few updates in the report view.

The ability be able to drill down on a line chart is great. I think that this is a welcome addition due to the fact that sometimes the line charts are the best visual and in the past this had to be modified to a bar chart in order to achieve this. Along with this they have also made the bar chart continuous on the axis when a date is selected.

They have also made inline hierarchies generally available, which is great when you want to create an on the fly hierarchy.

The matrix tables have also had some additions added so that you can use better styles.

And finally the ability to use your own custom color formatting for KPI visuals. As sometimes you want it to be a specific color to compliment your data.

Analytics

This is a totally new area that Power BI has created (as you can see in the first image above), and when I have been browsing other vendors in the visual space, this is where currently Power BI is still a bit behind. But with this addition I can soon see there will be a whole host of additions and improvements.

They have started out with some default analytics, but these are already going to help showcase or easily show trends within your data. And I have no doubt in future releases there will be a lot more analytics built into Power BI. There are a whole host of options when you got into any of the current options available.

Data Connectors

They have added additional data connectors for Snowflake, as well as making changes and improvements to the Impala, Web and SAP BW connectors.

What I want to highlight is the web connector, (again in the second image above) is that they have now made it very easy to navigate and make sure that the table that you want to select is the one that has the data that you want. They have made it as easy as to just select the table that you want.

Query Editing Improvements

Here they have updated the way the Merge/Append works to give you additional options.

Auto-Recover

Finally, they have enabled the functionality for Auto-Recovery of your Power BI Desktop files. I have used this in the past in the other Office suite (Word, Excel, etc) and this is a really handy feature to have. They have also provided the ability to set how frequently you want it to create an auto-recovery file.

You can find all the details here: Power BI Desktop August Feature Summary

Power BI – R Showcase

As I am sure a lot of people in the BI space know, currently R is gaining significant traction. This is partly due to the uses that it can be used for in the Data Scientist and visualizations. As well as now being part of SQL Server 2016 and Power BI.

It is great to see that they now have a showcase in Power BI, in which you can see the use cases.

You can find the blog post here: Inspire and Get Inspired with the R Showcase

Power BI – Content Pack Certification Program Overview

For organizations who are looking to create content packs that can be used within the Power BI Service, Microsoft have released a blog post explaining on how to go about this.

There do seem to be quite a few steps, but when I had a quick read it appears to be more of a process to get it done, and not very difficult.

You can find the details here: Content Pack Certification Program Overview

Power BI – Publisher for Excel August Update

There is an update for the Power BI publisher for Excel. This enables you to connect directly from Excel to your Power BI datasets and reports.

In this update they have given the ability to connect to read-only groups, as well as reports and datasets that have been shared with you.

You can find the information as well as the download link here: Power BI publisher for Excel – August update

OkViz – Sparkline Visual

The guys from OkViz have created another visual called Sparkline. And they have expanded the default options that you would traditionally have with a Sparkline chart.

I am sure that this will be used in a lot of reports and dashboards where it fits the requirement.

There is quite a bit of information and you can find it here: Introducing Sparkline for Power BI

Power BI – Creating Dashboard Items that show MTD (Month to Date), or WTD (Week to Date), or YTD (Year to Date) dynamically changing based on your current period

I have been working quite a lot with Power BI, and one of the great features is Q&A

So what if you wanted to create a dashboard item that would always show you your sales for the current month (MTD), or current week (WTD) or current Year (YTD)? And always show the correct current period, without you having to go and change it manually?

But you do not want to have to try and do this in your reporting layer, as this would affect all your reports, but want it to be used as it should as a dashboard?

What it looks like once finished.

As you can see below in my Q&A this is very easy to do. And the image below shows what it looks like once I have made a few changes to the initial Q&A question.

As you can see Power BI is context aware for dates, and it knows that we are in the month of August, and it is displaying our data from 01 Aug 2016 – 23 Aug 2016 (Todays date)

How to do it

In order to get the same visual as shown above I did the following:

First I typed it into Q&A and I got the following screen:

Next what I did was to click on the Visualizations, Filters and Fields on the right hand side to expand them.

I then clicked on the Visualizations and selected the Area Chart as shown below in the Visualizations section

What this did was then show me the following in the Canvas

The final step was for me to go into the Fields list and select a Date value from my Date table. And for my example I just chose the actual date. But you can choose any option you want to visualize.

NOTE: When you add in the Date, by default it will create the hierarchy in your Visualizations Axis as shown below.

I then clicked on the down arrow and selected Date

Once that is done you then get the same visual as first shown.

Other Examples

Likewise, if I change this to look at YTD (Year to Date) I change it to “This Year” and complete the steps above.

NOTE: Another thing to remember is that you can modify any of the Visualization
settings as you would with any Visual. As you can see above I changed the colour to Grey.

And finally I also created a dashboard item for Last Year

Tip – How to get the Dashboard Item from Q&A to link to your report

A quick tip is that if you still want your dashboard item you created above to go to your underlying report and NOT to your Q&A question once you have pinned your dashboard item complete the following.

First go into your report and click on the sheet that you want it to be linked to.

Now if you look in the URL you will see the URL as well as the ReportSection as shown below.

Now take that entire link, go into your dashboard item, click on the ellipses and then click on Tile Details

Then on in the tile details click on the Set Custom Link

And the paste in your URL from the steps above as well as ensure the option to “Open custom link in the same tab? Is set to No

Now when you click on the dashboard item it will take you to the correct sheet on your report.

Final notes

A final note is that it does appear that you can use “This year“, “Last Year“, “Last Two Years” etc. on any date field and it will apply.