Count the number of weeks

[Julia2023]

Hi, can you help count the number of weeks in the selected month? Instead of using the hardcoded value '4', it should calculate the number of weeks in the selected month.

Measure:

Budget = 'Budget'[Budget $ SUM]/4

Thanks!

[bhanu_gautam · Accepted solution]

@Julia2023<, First create a date table using below formula

Create a new table
DateTable =
ADDCOLUMNS (
CALENDAR (DATE(2020, 1, 1), DATE(2030, 12, 31)),
"Year", YEAR([Date]),
"Month", MONTH([Date]),
"MonthName", FORMAT([Date], "MMMM"),
"YearMonth", FORMAT([Date], "YYYYMM"),
"WeekOfYear", WEEKNUM([Date])
)

Then create a new measure in fact table to calculate number of weeks

dax
NumberOfWeeksInMonth =
VAR SelectedMonth = SELECTEDVALUE('DateTable'[Month])
VAR SelectedYear = SELECTEDVALUE('DateTable'[Year])
VAR FirstDayOfMonth = DATE(SelectedYear, SelectedMonth, 1)
VAR LastDayOfMonth = EOMONTH(FirstDayOfMonth, 0)
VAR FirstWeek = WEEKNUM(FirstDayOfMonth)
VAR LastWeek = WEEKNUM(LastDayOfMonth)
RETURN
LastWeek - FirstWeek + 1

Then update your measure

dax

Budget =

DIVIDE(

    SUM('Budget'[Budget $ SUM]),

    [NumberOfWeeksInMonth]

)

[Julia2023]

Hi @bhanu_gautam, thank you, this helped.