Calculating the First Monday of a Given Week in Kusto Query Language (KQL)

Hey KQL heroes! 🌟 If you’ve ever had to wrestle with dates and weeks in your analysis, you’ll know it can be quite a challenge. But worry not, because today we’re diving into a nifty Kusto Query Language (KQL) script that will make calculating the first Monday of any given week a breeze. Ready to uncover some KQL magic? Let’s get started!

A Quick Peek at Our KQL Script

Before we jump into the code, let’s break down what we’re aiming to do. This script will help you find the first Monday of a specified week in a year. Imagine the power of pinpointing exact dates for all your planning, scheduling, and analytical needs!

The Variables in Play

1. int_Offset: This variable represents the day of the week (0 = Sunday, 1 = Monday, …, 6 = Saturday).
2. firstDayOfYear: The date of the very first day of the year.
3. weekNumber: The week number for which we want to find the first Monday.

Step-by-Step Magic

1. Calculate the Day Offset:
   First, we need to know what day of the week the first day of the year lands on. This will help us calculate how many days we need to add to reach the first Monday.

let year = 2021; 
let firstDayOfYear = todatetime(strcat(year, "-01–01")); 
let int_Offset = toint(dayofweek(firstDayOfYear) / 1d);

2. Determine Days to Add:
Using a case statement, we figure out the exact number of days to add to the start of the week to get to Monday.

let addDays = case( 
      int_Offset == 0, 1
    , int_Offset == 1, 0
    , int_Offset == 2, 6
    , int_Offset == 3, 5
    , int_Offset == 4, 4
    , int_Offset == 5, 3
    , int_Offset == 6, 2
    , 0 
);

3. Calculate Start Date of the Given Week:
We then calculate the start date of the given week by adding (weekNumber - 1) * 7 days to the first day of the year.

let weekNumber = 17;
let startWeek  = firstDayOfYear + ((weekNumber - 1) * 7d)

4. Determine the First Monday:
Finally, we add the calculated number of days to the start date of the week to get the date of the first Monday of that week.

let firstMondayOfWeek = datetime_add('day', addDays, startWeek);
print firstMondayOfWeek

Bringing It All Together

Here’s the complete script that puts all these steps into action to find the first Monday of week 17 in 2021:

let year = 2021;
let weekNumber = 17;
let firstDayOfYear = todatetime(strcat(year, "-01-01"));
let int_Offset = toint(dayofweek(firstDayOfYear) / 1d);
let addDays = case(
    int_Offset == 0, 1
 ,  int_Offset == 1, 0
 ,  int_Offset == 2, 6
 ,  int_Offset == 3, 5
 ,  int_Offset == 4, 4
 ,  int_Offset == 5, 3
 ,  int_Offset == 6, 2
 ,  0
);
let startWeek = firstDayOfYear + ((weekNumber - 1) * 7d);
let firstMondayOfWeek = datetime_add('day', addDays, startWeek);
print Monday = firstMondayOfWeek

Wrapping Up

Understanding how to manipulate dates in KQL can supercharge your data analysis skills. This script is an example of how you can calculate the first Monday of any given week in a year, making your scheduling and reporting tasks much smoother.

Feel free to reach out if you have any questions or need further assistance. Until next time, keep your data dreams alive and kicking! 💡

Hope you enjoyed that! Let me know if you’d like any more tweaks or if there’s another topic you’re curious about!