DimDate: What, Why and How?

When you are first starting out with Power BI you may take for granted that everything just works. However, as you start to delve deeper into some calculations and try to compare values from this period to previous, or create cumulative totals, you will quickly discover the importance of a proper Date table. 

What is a DimDate table?

Calendar

A DimDate table MUST:

• have every single date for the entire calendar year 
• contain each date EXACTLY once
• be marked as a Date Table

A DimDate table SHOULD:

• span all dates in your data model (at a minimum those to be used in reporting/relationships)
• have a nicely formatted DateKey that can be used for relationships
• include any Date related information needed for reporting (such as Month, Quarter, Weekday, etc)

Why do we need a DimDate table?

If you have any date related information in your data model, get a robust DimDate table now. You might not have an immediate use for it, but I guarantee you will soon. Even Power BI built in Quick Measures need a DimDate table to provide accurate results. 

DimDate tables allow us to calculate trends over a fixed period, regardless of the number of transactions on any one date. Because our data model is non-contiguous when it comes to Dates, we cannot accurately graph monthly trends, see where there are gaps in sales, or computer year to date totals. The contiguous DimDate table makes all that possible. 

How do we get a DimDate table?

Some data sources already have a DimDate table built in (such as when querying from SQL server). This post is for those of you without a readily accessible DimDate table. 

The lazy way to create a DimDate table is to use the CALENDARAUTO() function in DAX, but lazy does not equal best. Any time you are adding columns or tables to the data model, it is generally more efficient to add them in Power Query, rather than DAX. 

Quickly Create a DimDate Table in Power Query (M code)

Follow the instructions below to create a quick, simple yet effective DimDate table in Power Query:

• From the Power Query Editor, in the Home tab, click New Source > Blank Query. 
• Rename the Query1 to DimDate in the Query Settings pane on the right (if you don't see this pane tick the box in the View tab in the ribbon to turn it on).
• Click Advanced Editor in the Home tab in the ribbon. 
• Replace all the code in the Advanced Editor window with the below code: 

let

// Basic Date table courtesy of www.excelwithallison.com

Credit = www.ExcelwithAllison.com,  

// Edit this start date to match your dataset.

startDate = #date(2020, 1, 1),

endDate = Date.EndOfYear(Date.From(DateTime.LocalNow())),

// Put the number for your financial end date

    FYMonthEnd = 6,

Dates = List.Dates(startDate, Duration.Days(endDate - startDate), #duration (1,0,0,0)),

#"Converted to Table" = Table.FromList(Dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),

#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),

    #"Inserted DateKey" = Table.AddColumn(#"Changed Type", "DateKey", each Date.ToText([Date],"yyyyMMdd"), type text),

    #"Inserted Year" = Table.AddColumn(#"Inserted DateKey", "Year", each Date.Year([Date]), Int64.Type),

    #"Inserted Quarter" = Table.AddColumn(#"Inserted Year", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),

#"Inserted Month Name" = Table.AddColumn(#"Inserted Quarter", "Month name", each Date.MonthName([Date]), type text),

#"Inserted Month" = Table.AddColumn(#"Inserted Month Name", "Month number", each Date.Month([Date]), Int64.Type),

    #"Inserted Day of Month" = Table.AddColumn(#"Inserted Month", "Day of month", each Date.Day([Date]), Int64.Type),

    #"Inserted Day of Year" = Table.AddColumn(#"Inserted Day of Month", "Day of Year", each Date.DayOfYear([Date]), Int64.Type),

    #"Inserted Day of Week" = Table.AddColumn(#"Inserted Day of Year", "Day of Week", each Date.DayOfWeek([Date]), Int64.Type),

    #"Inserted Day Name" = Table.AddColumn(#"Inserted Day of Week", "Day name", each Date.DayOfWeekName([Date]), type text),

    // In Week functions

    // 0 represents Sunday start

    // 1 represents Monday start

    // 2 represents Tuesday start

    #"Inserted Week of Year" = Table.AddColumn(#"Inserted Day Name", "Week of Year", each Date.WeekOfYear([Date],1), Int64.Type),

#"Inserted Week of Month" = Table.AddColumn(#"Inserted Week of Year", "Week of Month", each Date.WeekOfMonth([Date],1), Int64.Type),

    #"Added FY Month Number" = Table.TransformColumnTypes(Table.AddColumn(#"Inserted Week of Month", "Financial Month Number", each if [Month number] <= FYMonthEnd then [Month number]+ (12 - FYMonthEnd) else [Month number]- FYMonthEnd),{{"Financial Month Number", Int64.Type}}),

    #"Inserted FY Quarter" = Table.AddColumn(#"Added FY Month Number", "FY Quarter", each Number.IntegerDivide([Financial Month Number]-1, 3)+1, Int64.Type),

    #"Added FY Year" = Table.TransformColumnTypes(Table.AddColumn(#"Inserted FY Quarter", "Financial Year", each if [Financial Month Number] > [Month number] then [Year] else [Year]+1),{{"Financial Year", Int64.Type}})

in
    #"Added FY Year"

• Click Done to close the Advanced Editor.
• Select the startDate step from the Query Settings pane. 
• Change the year in the Formula Bar (tick the box in the View tab in the ribbon if you don't have a formula bar) to the year your data model dates begin.
• Select the endDate step to see that this DimDate table will automatically update to end on Today's date. Edit this step if you want to be able to forecast into future.
• Select the last step in the Query Settings pane.
• Use the options in the Add Column tab in the ribbon to add any additional Date information you might need. 
• Close and Apply. 

Date Key

You may notice that this M code contains a column for DateKey. I find that using columns with Date data type as the key relationship column can be unreliable. Using a column with text data type formatted consistently is much more reliable. I use "yyyyMMdd" format for all my date key columns in Power BI. 

To create a date key column, we can add a new custom column in Power Query. The M formula is:

= Date.ToText([Date], "yyyyMMdd")

Where [Date] is the name of your date column in your table. The date table above already has a date key created for you, so you simply need to add a DateKey column to any of your fact tables that you want to relate to this date table. 

See more about DateKey in this post: DateKey How to Relate Your Date Data

Mark As Date Table

The last step is to mark you table as a Date Table so Power BI will recognize it in all Time Intelligence calculations: 

Once you've successfully marked as date table, the icon next to your Date column will change to: 

Now you can use DAX time intelligence functions without errors.

Microsoft Teams FREE Helpline: Answers to Frequently Asked Questions

Today I helped answer your questions at the free Microsoft Teams Helpline all day today on the ACE Training website. It was great to see so many people logging in to share their questions, experiences and struggles.



No question is a silly question, Microsoft Teams is being updated and developed daily with new features and functionality coming at us fast.

To help you get around Teams like a seasoned pro, I've compiled some of the more frequently asked questions from our helplines over the past few weeks. If you don't see your question in the list below, leave a comment and I'll respond right away, providing you with the experience and knowledge to help you out with the technical how to as well as some best practice tips to make your conference calls more productive and less disruptive.

Meetings: Outlook and Microsoft Teams

Did you know that your Outlook calendar and Teams calendar are synced? Both calendars show you exactly the same appointments, just with different interface and functionality when creating the meeting.

I have multiple Teams meetings relating to one project, how can I best capture the minutes and video recordings in one place?

It's great that we can easily record meetings with Microsoft Teams, giving team members more flexibility over their schedules and fostering a transparent, collaborate work environment. The recordings are saved to Stream, Microsoft's video hosting platform, which is designed to handle large video files with ease. Meeting minutes taken using the Microsoft Teams meeting minutes button are stored in a 'Wiki' file in SharePoint or OneDrive. In order to make these resources easy to reference later, I recommend using the "Add channel" functionality for Microsoft Teams meetings. To truly get the most benefit from Microsoft Teams, you should be prefer using the Teams area over private chat and meetings. If you have an ongoing project with multiple meetings, files, videos and content to share then set up a Channel in Microsoft Teams dedicated to that project. Use this channel for all communication and files relating to the project. When scheduling meetings for the project team, use Microsoft Teams (not Outlook) to create the meeting and make sure to type the Project Channel name in the "Add channel" setting of the meeting invite. This will prompt Microsoft Teams to automatically save all meeting minutes in that project channel and will keep all recordings in one easy to find group channel within Microsoft Stream.

Bonus tip: Using channels for meetings also makes it easier for new team members to join meetings, view minutes of meetings that happened before they joined and generally promotes collaboration and transparency.

How do I invite external people to our Teams meeting? 

External invites are best sent from Outlook. If you have Teams desktop app installed on your computer, then just open up your Outlook calendar to schedule a meeting like you normally do. You will have a "New Teams Meeting" button available in the Home tab. This allows you to set up a meeting as you have always done, inviting both internal and external attendees. If you have already scheduled the meeting using Microsoft Teams, you can open the meeting in Outlook to invite external attendees.

How do I turn existing meetings into Teams meetings?

If you aren't using the built in meeting minutes functionality and aren't worried about attaching existing meetings to a channel, then you can use Outlook to convert any existing meeting into a Teams meeting. From your Outlook calendar, open an existing meeting and edit the series or single appointment. In the ribbon click the 'Teams Meeting' button and then 'Send update'. This will generate a Teams meeting link and enable everyone to join using Microsoft Teams.

Files: SharePoint, OneDrive and Microsoft Teams

Did you know that Microsoft Teams uses SharePoint to store all files you share within Teams? If you do this from a private chat, they will be saved to your personal OneDrive and shared with the other members of the chat. Best practice though is to share files using Teams channels. In this case, files are saved to SharePoint document library and accessible to anyone in the Team, even if they join the team after you've already shared the file. Great collaboration!

How do I attach a photo from my phone? 

The Teams mobile app allows you to attach files the same way you can in Teams desktop, by using the paperclip icon to attach files. If you click the paperclip icon at the bottom of a chat in Teams, you can attach photos or any other files to the post. The photo or file attachment will automatically be saved in the SharePoint document library for the Team channel in which you make the post. We have another option for sharing photos with the Teams mobile app. From your phone, you also can share the photo as an embedded chat image by using the photo icon. Unlike the paperclip, sharing photos using the image icon will NOT save a copy of the photo in SharePoint. Instead, it assumes you are sharing it purely for entertainment or morale purposes and makes the photo easy to see and enlarge from within the chat. 

If I create a private channel in Microsoft Teams does that mean that all posts and chats are only available to those members of the private channel?

Private channels were released in November, 2019 and enable you to add a second layer of security within a Team. Chats, files and posts made within a private channel are only visible to members of that private channel. Only the Team owner and members of that channel even know the channel exists. However, private channels do add a layer of complexity to the SharePoint site collection permissions and are still in development. Ensure you consider your options and weigh the pros and cons before creating lots of private channels. Leave me a message below if you want to discuss this further. 

Teams: Overview and functionality

Did you know that Microsoft Teams brings together Microsoft 365 apps and external apps into one handy place? Teams, channels, chat, calendar, calls and files can all be managed within Microsoft Teams. It is a highly customisable interface and you can make it your own hub for internal communication. Take time to get to know the features and discuss the best plan of attack with your team.  

I still "don't know what I don't know". What else can you tell me about Microsoft Teams? 

Check out my previous blog posts on Microsoft Teams, which includes some best practice tips and cheat sheets. Explore the built in 'Help' button in the bottom left of Microsoft Teams. It has a super handy 'What's New' feature that I check regularly and also has some helpful training videos and tips. The best way to learn is to start using it - share and collaborate with your team and others in your network. See how others are using it and adapt for your needs. 

As always, I'm here to help. I talk to clients daily about their joys, struggles and discoveries using Microsoft Teams and can help shine some light on how to get the most out of Microsoft Teams.

Use Parameters to Combine Data

There are many reasons you might need to combine multiple queries into one appended table, and the Power BI Get Data From Folder makes this process much easier for many data sources. However, it has some limitations that we can overcome through the use of parameters.

I have used paremeters to combine data from multiple Excel sheets, but in this post I am going to use data from a public website to demonstrate one use of parameters to combine data in Power BI.

Follow along using the NZ Population data on my site.

Stage A: Create a parameter values table and parameter. 

Before we can combine the data, we need to create a parameter and a list of values that will be used to populate that parameter.

1. Start with a blank Power BI report, Get Data from Web.
2. Paste the URL: https://sites.google.com/site/akdatasamples/nzpop/ and click OK.
3. Select Table 1 and Transform the query. Rename this query to NZ Population. 
4. In the Home tab in the ribbon, select Manage Parameters > New Parameter.
5. Fill in the Parameters properties:
1. Name: PopRegion, 
2. Type: Text, 
3. Current Value: north_island_a
   (note the current value is the same value as in the first row of our NZ Population table).

Stage B: Create sample file query using first subset of data.

Now we need to get our first sample file. In this example, that will be the table in the north_island_a regions on the North Island A subpage from the above website.

1. Select New Source, Web.
2. Paste the URL: https://sites.google.com/site/akdatasamples/nzpop/ and this time select 'Advanced'.
3. We have the first URL part, now we need the second URL part. Choose 'New Parameter' for the second URL part drop down. Name the parameter "PopRegion" and set the data type to 'text'. 
4. Set the current value to north_island_a
5. Click OK.to create the parameter.
6. Click OK to load from Web.
7. Select Table 1 from the Navigator pane and click OK.

You should see a table of NZ population from the North Island A regions of Northland, Waitemata, Auckland and Counties Manukau. Think of this as your 'sample query' that determines everything that must be done to the data before it can be combined.

Stage C: Create a custom function based on the sample query.

1. Right click on Table 1 in the queries pane, and choose 'Create Function'. 
2. Name the function 'GetPop'.

That's it! Power BI works its magic in the background and creates a custom function based off the sample file we created in the previous stage.

Stage D: Apply the custom function to each row of the parameter values table.

Now we just need to apply that custom function to each subpage in the NZ Population parameter values table we created in Stage A so that we can see the population for the entire country.

1. Select the NZ Population query (our parameter values table).
2. In the Add Column tab, select 'Invoke Custom Function'. 
3. Type 'PopTable' for the new column name. Select PopRegion for the function.
4. In the row that now appears, select the SubPage column as the parameter input for the custom function. 
5. Click OK. 

Power BI will now use the text in the SubPage column to populate the URL and get data from each sub page listed.

You should now have a table for each row/subpage of the NZ Population query. Click in the white space in the same cell as the word Table to see a preview of the table in the lower half of the screen.

Stage E: Expand the data custom function column.

Our final step is to expand the data from these tables and combine into one query. As you can from the table preview above, all the information we need is in the table itself, so we can remove our other two columns now. All you should be left with is the PopTable column of tables.

1. Click the double arrow in the column heading.
2. Keep Expand selected.
3. Tick Select All.
4. Untick Use original column name as prefix. 
5. Click OK.

Congratulations! You have successfully used parameters to combine data of similar structure into one long appended table. Set the data type for each column before you Close and load the data. See what insights you can gather about the NZ population.

Download the NZ Population.pbix file. 

How to finally sort out your emails by using the Four Ds

ACE were approached by a reporter from the NZ Herald network for comment on an article about sorting out your emails, and I was chosen as the resident Outlook expert and ACE spokeswoman. I teach a variety of Outlook courses, including one on Time Management using Outlook, where I follow up with clients in a one on one coaching environment to see how they are getting on with applying the techniques and tips I showed them.  

If you subscribe to the Rotorua Daily Post you may see my comments or photo in your local paper soon, but for those of you who don't I thought I would share some thoughts with my loyal readers as well.

‘Little and often’ is a great motto for handling your inbox. Try to empty your inbox after each visit. A simple yet effective way to do that is to process your messages by using the Four Ds (Do it now, Date activate it, Delegate it, Delete it) principle.  

Humans have a tendency to hoard things, but you shouldn’t be afraid to delete emails. I find it helps some clients to create a ‘bin’ folder instead of sending the emails directly to deleted items. Psychologically this can be easier than a permanent delete. While you’re at it, why not create a ‘Good News’ folder to file all those emails that make you feel good or tell you thanks. You can also setup some basic Outlook Rules to automatically file or delete messages you don’t need to read immediately.

No-one is immune to the odd moment or two of procrastination. But many of those emails in your inbox can probably be actioned in 5 minutes or less. If that’s the case, do it now. This will save you 5 minutes having to skim or scroll through the same email over and over. Plus it’s so satisfying to tick them off your ‘to do’ list. Setting up some Outlook Quick Steps can help make this easier and faster.
Don’t be afraid to ask for help. I often find clients are storing emails in their inbox simply because they don’t know what to do, or they’re waiting for a meeting to catch up with someone about it. It doesn’t do anyone any good just sitting in your inbox. Take positive action and forward/delegate the email as appropriate or ask for clarification as soon as you receive it.

Any email that doesn’t fit into those three categories (Do it now, Delegate it, Delete it) is important work for you to schedule for later (Date activate it). Did you know you can use your RIGHT mouse button to drag the email into Outlook tasks? Outlook magic! Plus you can use Outlook Categories, Search, and Search Folders to help organise your emails rather than waste time trying to maintain a complex, multilevel folder structure. In short, Outlook is full of features to help you better manage your email and help you move your to do list into Tasks you can action.

3 Tips for Successful Remote Communication

Remote training works! In fact, it can be even more effective than in person training provided you have the right tools.

I have been delivering remote training for over 3 years, but many of my clients are trying it out for the first time. Here's what they've had to say about it so far:

Awesome training! Worked really well, the remote session was great :) Allison was lovely, really helpful, understanding and knowledgeable. I'm really excited to start using Power BI.

- Kelsey Brooke-Cowden, Digital Acceleration Analyst @ Mars

Software perfectly enabled the course to be a success, great how Allison could see all of our screens without having to share them. Great examples, workbook was really easy to follow. Allison is a great tutor, very patient and friendly, and very knowledgeable and clearly passionate. Made for a great learning environment. Really really enjoyed the course :) Thank you.

- Kaitlin Grenside, Finance Assistant @ IT Partners Ltd 

  

There are a few simple things you can do to help ensure you get the most out of a remote training session or any remote communication. I've compiled a few tips below.

TIP 1: Use a headset to reduce noise

Ideally your "home office" should be quiet, well lit and away from distractions. Since not all of us are fortunate enough to have a spare room with a door to close, a headset can be the next best thing. Use a headset whenever on a conference call or training session to filter out some of the background noise for the benefit of your colleagues. Bonus tip: Put your headphones on while working on tasks that require high levels of concentration. This will signal to other people in your household (or 'bubble' as we call it here in NZ) that you're "in the zone".

TIP 2: Webcams add meaning to remote communication

According to Mehrabian's rule of personal communication, body language accounts for 55% of the meaning gathered from communication. Words only make up 7% of the meaning! That means a simple email or Teams chat might be misinterpreted, even a phone call provides less than half the meaning of in person communication. Turn on your webcam and we've got all three bases covered again for successful remote communication.

 TIP 3: Two screens are better than one

We all know two heads are better than one, well the same goes for screens. If you don't have two screens, split your single screen using the keyboard shortcut: Windows Key, Arrow. Try it now: hold down the windows key and tap the left arrow button. To get the most out of remote training or meetings, split your screen(s) between the content and the participants.

If you're using Microsoft Teams to work from home, join me on Tuesday, 14 April, 2020 between 8am-3pm NZST for a free Teams Helpdesk. Just hop on the live chat at ACE.co.nz and let us know your Teams question - we'll sort you out with the answer on the spot.

You can find the products I use and mentioned in this post on Amazon: 

    

New Zealand Region Map for Synoptic Panel Power BI Custom Visual

If you've attended one of my Power BI training sessions, you may have heard me recommend the Synoptic Panel Custom Visual for Power BI developed by OKViz. This visual is highly customisable and interactive without requiring any custom coding. It enables you to turn any image into a data chart, with the ability to customise colours based on data values from your live data model. I have used this visual for reporting health & safety incidents by location and body part, stock levels by factory region, and customer information by region.

I have had a few questions lately about how to easily map NZ data by region, so thought I would share my own NZ regional map that I have created. It is a regional map for New Zealand District Health Board regions to go alongside the NZ 2018 Census data that I often use for NZ reporting and statistics. When used in conjunction with the Synoptic Panel custom visual, you can create regional shaded maps for New Zealand based on your data to look something like this:

To use this visual, simply download the Synoptic Panel from the AppSource/Marketplace, and download my .svg file. Your data must have a column containing NZ region names matching the DHB spelling. Place this field in the categories field of the visual. Place whatever value you want to display (population, revenue, number of customers or sales or incidents) in the measure field. The image above was created using explicit states to set 5 distinct colours, but you can also turn on saturation to make the transition between colours more gradual based on the actual value of the measure. 

As always, post any questions or comments below and let me know how you get on!

Download the .svg map file and sample data from my sample data site.