Count the number of weeks

Julia2023 — Tue, 25 Jun 2024 13:24:19 GMT

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!

Re: Count the number of weeks

bhanu_gautam — Tue, 25 Jun 2024 13:39:37 GMT

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]

)

Re: Count the number of weeks

Julia2023 — Thu, 04 Jul 2024 14:14:43 GMT

Hi bhanu_gautam, thank you, this helped.

topic Re: Count the number of weeks in DAX Commands and Tips

Count the number of weeks

Re: Count the number of weeks

Re: Count the number of weeks