Need help to create week number

[Jose_Freire]

Hi, 

I´m creating a new Dashboard and I need a week number to represent in a bar graphic. Similar to this:

How can I create a WeekNum column?

https://drive.google.com/file/d/1o4QRpKhBBjzIdlIdfG_of583fCPVeYCs/view?usp=sharing https://drive.google.com/file/d/1o4QRpKhBBjzIdlIdfG_of583fCPVeYCs/view?usp=sharing 

Thaanks a lot!

[v-eqin-msft · Accepted solution]

Hi @Jose_Freire ,

According to my understand, you want to calculate the Week Number of each month , then use Month as the parent X-axis and Week Number as  sub X-axis, right?

You could add a Date column based on Month, MonthID and Day columns like this:

Date =
DATE ( LEFT ( [MontID], 4 ), RIGHT ( [MontID], 2 ), [Day] )

Then use the following formula to create the Week Number column:

WeekNumber =
1 + WEEKNUM ( [Date] )
    - WEEKNUM ( STARTOFMONTH ( 'Tabla1'[Date] ) )

Please follow the steps shown in the below gif:

Here is the pbix file.

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.

Best Regards,
Eyelyn Qin

[FrankAT]

Hi @Jose_Freire 

what is your definition of a weeknumber of the month? When starts the first week oh month?

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[amitchandak]

@Jose_Freire , Week of month you can refer

Start Month = STARTOMONTH('Date'[Date])
WeekDay = WEEKDAY([Date],2) //monday
Start of Week = [Date] -[WeekDay]+1 //monday
Month Week = QUOTIENT(DATEDIFF(Minx(FILTER('Date',[Start Month]=EARLIER([Start Month])),'Date'[Start of Week]),[Date],DAY),7)+1

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

[rogletree]

Hey, there's a power query function called WeekOfMonth() that should be able to do what you want.

https://docs.microsoft.com/en-us/powerquery-m/date-weekofmonth

In order to get your x-axis similar to your picture, you could create a new custom column and do something like:

Custom

= Date.Month([*yourDateField*]) + Date.WeekOfMonth([*yourDateField*])

If that doesn't quite work try using Text.From() and putting the above formula inside the parenthesis. It will make the column as text data type.

There may be a better way to do it but that's just what I thought of off the top of my head.