{"id":3972,"date":"2022-02-01T00:34:26","date_gmt":"2022-02-01T08:34:26","guid":{"rendered":"https:\/\/SUMMALAI.COM\/?p=3972"},"modified":"2022-02-01T00:34:28","modified_gmt":"2022-02-01T08:34:28","slug":"how-to-calculate-week-start-date-and-week-end-date","status":"publish","type":"post","link":"https:\/\/SUMMALAI.COM\/?p=3972","title":{"rendered":"How to Calculate Week-Start Date and Week-End Date"},"content":{"rendered":"\n

What\u2019s the easiest way to calculate your weekly metrics when your data has a daily granularity? Simply create a new calculated column for the \u201cweek start date\u201d or \u201cweek end date\u201d to use in your visuals! This is very easy to do, but maybe a little counterintuitive to the new Power BI user. Let\u2019s dive into a quick example on how to set this up.<\/p>\n\n\n\n

Let\u2019s say that I don\u2019t necessarily care how much revenue I made on 1\/7\/2013, but rather how much revenue I made the week starting on 1\/7\/2013 (which was a Monday) and ending on 1\/13\/2013 (Sunday). This is easy to do with a little DAX.<\/p>\n\n\n\n

I am simply going to create a calculated column on my data table and use the following DAX formula:<\/p>\n\n\n\n

if the week starts on Mondays, then<\/p>\n\n\n\n

Week Start Date = Data[Date] \u2013 WEEKDAY(Data[Date],2) + 1<\/em><\/strong><\/p>\n\n\n\n

If the week startson Sundays, then<\/p>\n\n\n\n

Week Start Date = Data[Date] \u2013 WEEKDAY(Data[Date],1) + 1<\/em><\/strong><\/p>\n\n\n\n

Below, please see the detail calculation when week starting on Mondays.<\/p>\n\n\n\n

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

You can see that certain dates are grouped to ranges of 7 days. For example, 1\/1\/2013 was a Tuesday so its \u201cWeek Start Date\u201d is Monday, December 31st<\/sup> 2012. Another example is 1\/9\/2019 which was a Wednesday. This means that its week start date was 1\/7\/2019.<\/p>\n\n\n\n

The formula is simple but confusing until you break it down. I\u2019m going to remove part of the formula and show each step with pictures. In this next image, I am only showing what the WEEKDAY function does:<\/p>\n\n\n\n

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

Simply put, it returns the day number of the date depending on the parameter you chose. In the above example, I chose a parameter of 2 which sets Monday as 1, Tuesday as 2, Wednesday as 3, etc.<\/p>\n\n\n\n

In this next picture, I am going to add back the first part of the final formula.<\/p>\n\n\n\n

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

We are taking our original date and subtracting the WEEKDAY number from the previous step. The first row, for example, would be 1\/1\/2013 \u2013 2 days = 12\/30\/2013. At this point, we can check which day that 12\/30\/2013 was and it was actually a Sunday, so all we need to do is add 1 day back to the calculation to give us Monday. This brings us to the original formula, shown again below:<\/p>\n\n\n\n

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

Take a couple minutes to let the logic sink in. It can be very confusing but it clicks after a while. Similarly, we can calculate the \u201cWeek End Date\u201d with a small change in the logic. Simply add 7 to the output instead of 1.<\/p>\n\n\n\n

Week End Date = Data[Date] \u2013 WEEKDAY(Data[Date],2) + 7<\/p>\n\n\n\n

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

Finally, we can easily use these calculated columns in any of our visualizations.<\/p>\n\n\n\n

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

And there you have it! Our revenue is grouped on the weekly level instead of on the daily level. This is extremely easy to do with just a bit of DAX!<\/p>\n\n\n\n

Ref: https:\/\/bielite.com\/blog\/week-start-date-power-bi-dax\/<\/p>\n","protected":false},"excerpt":{"rendered":"

What\u2019s the easiest way to calculate your weekly metrics when your data has a daily granularity? Simply create a new calculated column for the \u201cweek start date\u201d or \u201cweek end date\u201d to use in your visuals! This is very easy to do, but maybe a little counterintuitive to the new Power BI user. Let\u2019s dive Read More<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[10,621],"tags":[1089,1090],"class_list":["post-3972","post","type-post","status-publish","format-standard","hentry","category-microsoft","category-power-bi","tag-week-start-date-and-week-end-date","tag-week-start-date-and-week-end-date-power-bi"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3972","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3972"}],"version-history":[{"count":1,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3972\/revisions"}],"predecessor-version":[{"id":3974,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3972\/revisions\/3974"}],"wp:attachment":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3972"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3972"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3972"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}