Need help with DAX Formula to get Month and Week number of current month

[mrrobottelg]

Hi,

Need help with DAX Formula to get Month and Week number of current month.

Sample table

Date	Month Name	Day	Week	Year
6/29/2024	Jun	Sat	WK5	2024

[mrrobottelg]

Ok.

This also helps. Thank you.

[mrrobottelg]

Hi Ranjendra Garu,

I want Auto generated seperate table for everymonth.

[rajendraongole1]

Hi @mrrobottelg -Power BI does not support dynamic table creation in the DAX model as a seperate tables for each month.

you can try to create the same in Power query editor if you have a date table, by using custom functions.

Hope it helps you.

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[rajendraongole1 · Accepted solution]

Hi @mrrobottelg - Using calculated colum we can achieve this, extract the month name and the week number of the current month from a date column as below by creating two calculated columns for month and week

Month Name calculated column:

your date table.

Month Name = FORMAT('YourTable'[Date], "MMM")

Month No calculated column: 

Month No= Month ( YourTable[Date])

Weekin month calculated column:

Week In Month =
VAR FirstOfMonth = DATE(YEAR('YourTable'[Date]), MONTH('YourTable'[Date]), 1)
VAR WeekOfYear = WEEKNUM('YourTable'[Date])
VAR WeekOfMonth = WEEKNUM('YourTable'[Date]) - WEEKNUM(FirstOfMonth) + 1
RETURN
"WK" & WeekOfMonth

It works. Please check and replace with your date table instead of YourTable.

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!