cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jderekc
Helper IV
Helper IV

DAX formula to show week of the month, not week of the year

Hi all,

 

I've been browsing the internet for ways to show the current week of any given month but all I've come across is week number of the year.  Is it possible to show week of the month?  For instance, instead of week 31/32, it would be week 1 of August.  This may pose a problem if weeks carryover from one month to another though.  See my screenshot below for how it currently looks.  Also, my existing DAX query is also below:

 

daxweeks.PNG

 

DAX formula:

Week = " Week " & WEEKNUM(VendDisplay[DateCreated], 2) & " - " & FORMAT(VendDisplay[DateCreated], "MMMM")

Any help is appreciated as always.  Thanks!

 

Regards,

Derek

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @jderekc,

 

Try this column:

 

Week = " Week " & 1 + WEEKNUM ( VendDisplay[DateCreated] ) – WEEKNUM ( STARTOFMONTH ( VendDisplay[DateCreated] ) ) & " - " FORMAT(VendDisplay[DateCreated], "MMM")

If not works, my recomendation is to use a Calendar table linked to VendDisplay[DateCreated], and then change VendDisplay[DateCreated] to Calendar[Date] in this formula.

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

Hi @jderekc,

 

Try this column:

 

Week = " Week " & 1 + WEEKNUM ( VendDisplay[DateCreated] ) – WEEKNUM ( STARTOFMONTH ( VendDisplay[DateCreated] ) ) & " - " FORMAT(VendDisplay[DateCreated], "MMM")

If not works, my recomendation is to use a Calendar table linked to VendDisplay[DateCreated], and then change VendDisplay[DateCreated] to Calendar[Date] in this formula.

View solution in original post

@jderekc 

Can you please provide the solution here.

You are a genius, @Anonymous!  I did it and now my bar chart returns "Week 1 - Aug", "Week 2 - Aug", et cetera.  I did have to reformat the DAX formula just a little bit because it gave me an issue that completely failed its execution, but it was just a minor tweak.  Thanks so much!

 

Regards,

Derek

Its not working correctly...

https://1drv.ms/u/s!Aon2AsIzlG3tiyp74f_tQlYQaFbc

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

html:
Carousel MBAS ARun Part 1 768x460

MBAS 2021

Join Arun Ulagaratchagan and the Power BI team at MBAS! #MSBizAppsSummit #CommunityRocks

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!