trying to find out week of year in kql

Question

let month = 12;
let year = 2024;
let base = 
range day from startofday(now() - 1000d) to startofday(now()) step 1d
| where datetime_part("Month", day) == month and datetime_part("Year", day) == year
| project Week_Of_Year = datetime_part("Week_Of_Year", day), Month_Of_Year = datetime_part("Month", day)
| distinct Week_Of_Year, Month_Of_Year
| project Week_Of_Year, Month_Of_Year;

let max_week = 
    base
    | summarize Max_Week = max(Week_Of_Year);

max_week
| extend Adjusted_Week = iif(Week_Of_Year == 1 and Month == 12, Max_Week + 1, Week_Of_Year)

trying this but getting error EM0100: 'extend' operator: Failed to resolve column or scalar expression named 'Week_Of_Year'" i want thre week or month of year if 1 or 12 so it add 1 to the max of the week.

Answer 1

if month =12 and year =2024 so i want 48,49,50,51,52,53 max of week that is 52 +1 SO 53 SHOULD BE THERE

You can use below KQL Query to do that:

let ri_mnth = 12;
let ri_yr = 2024;
let rith_table = 
    range day from startofday(now() - 1000d) to startofday(now()) step 1d
    | where datetime_part("Month", day) == ri_mnth and datetime_part("Year", day) == ri_yr
    | project Week_Of_Year = datetime_part("Week_Of_Year", day), Month_Of_Year = datetime_part("Month", day)
    | distinct Week_Of_Year, Month_Of_Year;
let ri_max_wk = 
    rith_table
    | summarize Max_Week = max(Week_Of_Year);
rith_table
| extend Max_Week = toscalar(ri_max_wk)
| extend Rith_Adjusted_wk = iif(Week_Of_Year == 1 and Month_Of_Year == ri_mnth, Max_Week + 1, Week_Of_Year)
| project Week_Of_Year, Rith_Adjusted_wk, Month_Of_Year

Output:

Fiddle.