{"id":919,"date":"2023-06-21T12:14:09","date_gmt":"2023-06-21T04:14:09","guid":{"rendered":"https:\/\/www.info-trek.com\/blog\/?p=919"},"modified":"2024-05-02T12:09:12","modified_gmt":"2024-05-02T04:09:12","slug":"calendar-table-with-dax-part-4","status":"publish","type":"post","link":"https:\/\/www.info-trek.com\/blog\/calendar-table-with-dax-part-4\/","title":{"rendered":"CALENDAR TABLE WITH DAX Part.4"},"content":{"rendered":"\n

In the previous article<\/a> I\u2019ve talked about how to extract date component like Month and quarter. We\u2019re going to continue the other date component here<\/p>\n\n\n\n

In this part, we\u2019re going to talk about how to extract Week Number, Financial Year. Plus I also added the importance of \u201cMark as Table\u201d and the trouble you\u2019re going to run into while sorting the text-based date component.<\/p>\n\n\n\n

Extract Week_Num<\/h2>\n\n\n\n

If you do analyse your data by week, definitely not to skip this section. We\u2019re 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.<\/p>\n\n\n\n

We will be using Weeknum() function to extract the week number.<\/p>\n\n\n\n

=Weeknum(\u2018date\u2019[Date],2)<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Extract Weeknum<\/h2>\n\n\n\n

This part we simply are going to create a text based Weeknum column, concatenating the letter \u201cW\u201d with the Week_num column.<\/p>\n\n\n\n

=\u201dW\u201d&\u2019date\u2019[Week_num]<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Extract Year<\/h2>\n\n\n\n

It\u2019s time to extract the year. The function is very straight forwards, just like the Month() function. For the year, we\u2019ll be using Year().<\/p>\n\n\n\n

=Year(\u2018date\u2019[Date])<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Extract Financial_Year<\/h2>\n\n\n\n

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\u2019re going to talk about the unconventional type (haha, which means the financial cycle does not begins from January).<\/p>\n\n\n\n

Whichever month the financial cycle, the trick is almost th4e same. We\u2019re going to use IF() function to extract the Financial year.<\/p>\n\n\n\n

In the clip I demo if the financial cycle begins from April.<\/p>\n\n\n\n

=IF(\u2018date\u2019[Month]>=4,\u2019date\u2019[Year]+1,\u2019date\u2019[Year])<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Example, if the financial cycle begins from the month of July, this is how the IF function is going to looks like.<\/em><\/p>\n\n\n\n

=IF(\u2018date\u2019[Month]>=7,\u2019date\u2019[Year]+1,\u2019date\u2019[Year])<\/p>\n\n\n\n

Example, if the financial cycle begins from the month of November, this is how the IF function is going to looks like.<\/em><\/p>\n\n\n\n

=IF(\u2018date\u2019[Month]>=11,\u2019date\u2019[Year]+1,\u2019date\u2019[Year])<\/p>\n\n\n\n

Text-Based Financial year FY<\/h2>\n\n\n\n

Joining the text \u201cFY\u201d with the extracted Financial Year from before.<\/p>\n\n\n\n

=\u201dFY\u201d&\u2019date\u2019[Financial year]<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Mark as Date Table<\/h2>\n\n\n\n

Once you have done creating and setting up the calendar table, please do not forget to \u201cMark As Date Table\u201d. This is being one of the most important section after creating the whole date dimension. \u201cMark as Date Table\u201d allow all the Time Intelligence DAX functions in Power Pivot works smoothly like a baby arse!, Trust me, you don\u2019t want to run into error while you\u2019re on high building date based measures. Frustrating, I know!<\/p>\n\n\n\n

You will be prompted a dialog box of setting up the unique column.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

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.<\/p>\n\n\n\n

If from the beginning you\u2019re 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\u2019re creating any measures using date or time related DAX functions you might run into errors.<\/p>\n\n\n\n

Let me give you a very simple example. Let\u2019s 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.<\/p>\n\n\n\n

So, trust me. Don\u2019t 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!<\/p>\n\n\n\n

This is how to enable the \u201cMark as Date Table\u201d feature.<\/em><\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Sort data by Month Names<\/h2>\n\n\n\n

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\u2019re talking about all the text based date component that we created earlier, Power Pivot simply treat these columns as string.<\/p>\n\n\n\n

Which means when you try to sort or filter these columns, you can\u2019t sort by month from Jan to Dec. Instead data will be sort alphabetically, refer the figure below.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

To solve this problem, you will have to set up how the column is going to be sorted.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

The text-based column is going to be sort by the number version of its.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Do this to all the text-based columns.<\/p>\n\n\n\n

Once you have completed the setup, this is what you will be getting.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

If you\u2019re ready to learn how to harness\u00a0Microsoft Office\u00a0to get the most secure Office for your business, you\u2019re in the right place. These learning opportunities can help you get started quickly\u2014from product exploration to deep training and certification.<\/p>\n","protected":false},"excerpt":{"rendered":"

In the previous article I\u2019ve talked about how to extract date component like Month and quarter. We\u2019re going to continue the other date component here In this part, we\u2019re going to talk about how to extract Week Number, Financial Year. Plus I also added the importance of \u201cMark as Table\u201d and the trouble you\u2019re going to run […]<\/p>\n","protected":false},"author":4,"featured_media":581,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[1],"tags":[],"class_list":["post-919","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-uncategorized"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/posts\/919","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/comments?post=919"}],"version-history":[{"count":2,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/posts\/919\/revisions"}],"predecessor-version":[{"id":1633,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/posts\/919\/revisions\/1633"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/media\/581"}],"wp:attachment":[{"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/media?parent=919"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/categories?post=919"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/tags?post=919"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}