In the previous article I’ve talked about how to extract date component like Month and quarter. We’re going to continue the other date component here
In this part, we’re going to talk about how to extract Week Number, Financial Year. Plus I also added the importance of “Mark as Table” and the trouble you’re going to run into while sorting the text-based date component.
Extract Week_Num
If you do analyse your data by week, definitely not to skip this section. We’re going to extract the week number from the Date column. The amount weeks in a year roughly falls between 52-54, depending on whether it is a leap year or whether the week starts from Sunday or Monday.
We will be using Weeknum() function to extract the week number.
=Weeknum(‘date’[Date],2)
Extract Weeknum
This part we simply are going to create a text based Weeknum column, concatenating the letter “W” with the Week_num column.
=”W”&’date’[Week_num]
Extract Year
It’s time to extract the year. The function is very straight forwards, just like the Month() function. For the year, we’ll be using Year().
=Year(‘date’[Date])
Extract Financial_Year
This is a tricky one. For the conventional type of financial cycle (starts from January), I guess you can refer to the text-based quarter section. We’re going to talk about the unconventional type (haha, which means the financial cycle does not begins from January).
Whichever month the financial cycle, the trick is almost th4e same. We’re going to use IF() function to extract the Financial year.
In the clip I demo if the financial cycle begins from April.
=IF(‘date’[Month]>=4,’date’[Year]+1,’date’[Year])
Example, if the financial cycle begins from the month of July, this is how the IF function is going to looks like.
=IF(‘date’[Month]>=7,’date’[Year]+1,’date’[Year])
Example, if the financial cycle begins from the month of November, this is how the IF function is going to looks like.
=IF(‘date’[Month]>=11,’date’[Year]+1,’date’[Year])
Text-Based Financial year FY
Joining the text “FY” with the extracted Financial Year from before.
=”FY”&’date’[Financial year]
Mark as Date Table
Once you have done creating and setting up the calendar table, please do not forget to “Mark As Date Table”. This is being one of the most important section after creating the whole date dimension. “Mark as Date Table” allow all the Time Intelligence DAX functions in Power Pivot works smoothly like a baby arse!, Trust me, you don’t want to run into error while you’re on high building date based measures. Frustrating, I know!
You will be prompted a dialog box of setting up the unique column.
The unique identifier will be the first column of the date. This column contains every day between the years you have imported into the Data Model window, no repetitive of any of the dates.
If from the beginning you’re still very much frustrated of why you have to create this date dimension table. Well, I will explain it here. The reason why this date dimension table has to contains all dates even though your business does not run daily (like public holiday and etc) is because in future, when you’re creating any measures using date or time related DAX functions you might run into errors.
Let me give you a very simple example. Let’s say you would want to compare the sales same time/date from last year (maybe the same date last year was a replacement holiday, or it falls on the day which your business is not operating), you will want to be able to compare that.
So, trust me. Don’t skip this step if you are creating your data model, whether with Power BI, Power Pivot or Power Query, I cannot stress how important this is!
This is how to enable the “Mark as Date Table” feature.
Sort data by Month Names
We will run into a small lil problem while generating the text-based data component earlier. Power Pivot does not recognize the month in text format, neither is the text-based financial year column. Basically we’re talking about all the text based date component that we created earlier, Power Pivot simply treat these columns as string.
Which means when you try to sort or filter these columns, you can’t sort by month from Jan to Dec. Instead data will be sort alphabetically, refer the figure below.
To solve this problem, you will have to set up how the column is going to be sorted.
The text-based column is going to be sort by the number version of its.
Do this to all the text-based columns.
Once you have completed the setup, this is what you will be getting.
If you’re ready to learn how to harness Microsoft Office to get the most secure Office for your business, you’re in the right place. These learning opportunities can help you get started quickly—from product exploration to deep training and certification.
