Calculations that make comparisons to previous quarters or to the same period one year ago are very important in all business intelligence tools. DAX, the formula language in PowerPivot, allows you to define such calculations by means of Time Intelligence Functions.
However, in order to use these, you need to include a Date dimension table in your PowerPivot model. When you do not have access to a date table in your ICT network, you will be able to create one on the spot after reading this post.
A Date dimension table is a table of dates. It contains one row for every date that might occur in your dataset to be analyzed. Here is a quick scenario to do it.
Open an Excel sheet, and enter the very first date that might play a role in your PowerPivot model. Let us assume this date is the 1st of January 2014. In the top left cell of your Excel sheet (cell A1) enter the label for your “Date” column. In the cell below, put the value 1/1/2014. Select the cell that contains the first date, and then drag the fill handle across the adjacent cells below the active cell. Drag it all the way down, until your column of dates is complete.
Be sure to indicate that there is a header row.
In the PowerPivot tab, select one of the cells of your table, and click Add to Data Model.
In the PowerPivot model, you can rename your table as ‘Date’. Your Date column is automatically recognized as a Data Data Type.
Next, tell the PowerPivot model that this table, which happens to name “Date”, is actually going to be used as a Date (dimension) table in the model. From the Design tab, click Mark as Date Table and then choose the column which holds the real date values. In this example, that column also bears the name Date (how convenient from us).
We’ll complete our Date dimension by defining new columns so we can slice and dice by ‘Year’, ‘Quarter’, et cetera. These columns are defined as calculated columns. Here is the list of columns with their formulas.
Year
=YEAR(‘Date'[Date])
Month
=MONTH(‘Date'[Date])
Day
=Day(‘Date'[Date])
WeekDayNr
=WEEKDAY(‘Date'[Date])
MonthNameShort
=SWITCH([Month], 1, “Jan”, 2, “Feb”, 3, “Mar”, 4, “Apr”, 5, “May”, 6, “Jun”, 7, “Jul”, 8, “Aug”, 9, “Sep”, 10, “Oct”, 11, “Nov”, 12, “Dec”, “Unknown month number” )
WeekDay
=SWITCH([WeekDayNr], 1, “Sun”, 2, “Mon”, 3, “Tue”, 4, “Wed”, 5, “Thu”, 6, “Fri”, 7, “Sat”,”Unknown day” )
Quarter =SWITCH(‘Date'[Month],1,”Q1″,2,”Q1″,3,”Q1″,4,”Q2″,5,”Q2″,6,”Q2″,7,”Q3″,8,”Q3″,9,”Q3″,10,”Q4″,11,”Q4″,12,”Q4″,”Unknown quarter”)
YearMonth
=’Date'[Year] & ” ” & ‘Date'[MonthNameShort]
YearQuarter
=’Date'[Year] & ” ” & ‘Date'[Quarter]
IsWeekend
=IF(‘Date'[WeekDay] = “Sat” || ‘Date'[WeekDay]=”Sun”,TRUE(),FALSE())
We also need a DateKey column as a unique identifier of Date rows:
DateKey
=YEAR(‘Date'[Date]) & RIGHT(“0” & MONTH(‘Date'[Date]),2) & RIGHT(“0” & Day(‘Date'[Date]),2)
And here is our Date dimension:
The values of the numeric attributes are sorted numerically. The values of the non numeric attributes are sorted alphabetically which is fine for attributes Quarter and Year Quarter. However, for the attributes MonthNameShort, WeekDay and YearMonth, this alphabetical order doesn’t match the logical time order. Adjust the ordering of these columns and have them sorted by another column that has the correct order.
You can sort MonthNameShort by Month. The column WeekDay by can be sorted by WeekDayNr.
For the YearMonth, containing values like 2015 Jan, 2015 Feb, .. , the sorting can be done by a numeric column containting the year and month part (YYYYMM format). This YYYYMM column can quickly be derived with this formula:
YYYYMM
=’Date'[Year] & RIGHT(0 & ‘Date'[Month],2)
You can new sort YearMonth by YYYYMM .
The new column can be hidden from the end user model. Right-click the the column name, and click Hide from Client Tools.
Your date dimension, created from a single value, is now ready for use.
More about this topic on MSDN is here.
Cookie | Duration | Description |
---|---|---|
ARRAffinity | session | ARRAffinity cookie is set by Azure app service, and allows the service to choose the right instance established by a user to deliver subsequent requests made by that user. |
ARRAffinitySameSite | session | This cookie is set by Windows Azure cloud, and is used for load balancing to make sure the visitor page requests are routed to the same server in any browsing session. |
cookielawinfo-checkbox-advertisement | 1 year | Set by the GDPR Cookie Consent plugin, this cookie records the user consent for the cookies in the "Advertisement" category. |
cookielawinfo-checkbox-analytics | 11 months | This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics". |
cookielawinfo-checkbox-functional | 11 months | The cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional". |
cookielawinfo-checkbox-necessary | 11 months | This cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary". |
cookielawinfo-checkbox-others | 11 months | This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other. |
cookielawinfo-checkbox-performance | 11 months | This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance". |
CookieLawInfoConsent | 1 year | CookieYes sets this cookie to record the default button state of the corresponding category and the status of CCPA. It works only in coordination with the primary cookie. |
elementor | never | The website's WordPress theme uses this cookie. It allows the website owner to implement or change the website's content in real-time. |
viewed_cookie_policy | 11 months | The cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data. |
Cookie | Duration | Description |
---|---|---|
__cf_bm | 30 minutes | Cloudflare set the cookie to support Cloudflare Bot Management. |
pll_language | 1 year | Polylang sets this cookie to remember the language the user selects when returning to the website and get the language information when unavailable in another way. |
Cookie | Duration | Description |
---|---|---|
_ga | 1 year 1 month 4 days | Google Analytics sets this cookie to calculate visitor, session and campaign data and track site usage for the site's analytics report. The cookie stores information anonymously and assigns a randomly generated number to recognise unique visitors. |
_ga_* | 1 year 1 month 4 days | Google Analytics sets this cookie to store and count page views. |
_gat_gtag_UA_* | 1 minute | Google Analytics sets this cookie to store a unique user ID. |
_gid | 1 day | Google Analytics sets this cookie to store information on how visitors use a website while also creating an analytics report of the website's performance. Some of the collected data includes the number of visitors, their source, and the pages they visit anonymously. |
ai_session | 30 minutes | This is a unique anonymous session identifier cookie set by Microsoft Application Insights software to gather statistical usage and telemetry data for apps built on the Azure cloud platform. |
CONSENT | 2 years | YouTube sets this cookie via embedded YouTube videos and registers anonymous statistical data. |
vuid | 1 year 1 month 4 days | Vimeo installs this cookie to collect tracking information by setting a unique ID to embed videos on the website. |
Cookie | Duration | Description |
---|---|---|
ai_user | 1 year | Microsoft Azure sets this cookie as a unique user identifier cookie, enabling counting of the number of users accessing the application over time. |
VISITOR_INFO1_LIVE | 5 months 27 days | YouTube sets this cookie to measure bandwidth, determining whether the user gets the new or old player interface. |
YSC | session | Youtube sets this cookie to track the views of embedded videos on Youtube pages. |
yt-remote-connected-devices | never | YouTube sets this cookie to store the user's video preferences using embedded YouTube videos. |
yt-remote-device-id | never | YouTube sets this cookie to store the user's video preferences using embedded YouTube videos. |
yt.innertube::nextId | never | YouTube sets this cookie to register a unique ID to store data on what videos from YouTube the user has seen. |
yt.innertube::requests | never | YouTube sets this cookie to register a unique ID to store data on what videos from YouTube the user has seen. |
Cookie | Duration | Description |
---|---|---|
WFESessionId | session | No description available. |