Get start date of the week for the month in the given date in Tableau

0
votes

I am trying to get the weeknumber for a month from a give date. using following method:

Created a calculated field for 'week of the year': as A

DATEPART('week',[Date],'monday')

created a calculated field for "week of start date" : as B

 DATEPART('week',DATETRUNC('month',[Date]),'monday')

Hence week =

 A-B+1

Also, I am calculating Week Start Date using:

DATETRUNC('week',[Date],'monday')

I am facing one issue: For instance, date: 08/31/2020 ( This is monday) This is showing as week 6 for August as well as week 1 for September which it should not show. Week 1 for September should be 09/01/2020.

Can anyone help me on this?

1
datetableau-api
How any function can return two values? That is against the definition of a function! please show me the screenshot where you are getting two values for 08/31/2020. I am also unable to understand why do you want week 1 for 09/01/2020 when the week is not starting on Monday? Do you want the values as given in solution proposed by me? please check – AnilGoyal

1 Answers

1
votes

Create a calculated field 'desired result'

floor(DAY([Date])/7)+1

and you should be able to get what you want. Please check on some sample records

enter image description here