trying to get a the week number of the year for a certain date

[mikep7521]

Hello, Please see pictures attached. You can see in the first picture that I used Column from Example to get the week of the year. The values are right in that column. but when i try to use that same column in a table (picture 2) you can see that the nvalues are not correct. Why is this? I already attempted to do a regular column and I was receiving the same wrong answers.

[mikep7521 · Accepted solution]

I figured out the issue. Thanks for everyone's help. There were multiple installments closing on the same date so the weeknum function was totaling all the week numbers together for multiple installments with the same close date.

[mikep7521]

In picture I attached you can see the very simple formula using the weeknum function. When you look in the table you will see that the outputs are incorrect. Why am I not receiving the correct answer?

[ChrisMendoza]

@mikep7521,

Which 'Date' are you using for your filter context in your table visual?

For instance, in my sample :

Column = WEEKNUM(Table1[SubmittedDate],2)

Column 2 = WEEKNUM(Table1[ApprovedDate],2) 

*notice they are taking the WEEKNUM( ) of different dates

Produces different numbers based on the filter context:

My guess is that you are not using a proper 'Date' Dimension table that is related to your 'Fact' table thereby giving you unexpected values.

[mikep7521]

I'm trying to find the week number for the Close Date field. The only filter on this table is that the close date is in 2018. What do you mean by using a proper 'Date' Dimension table that is related to the table I'm displaying in the picture I added on my original post? the close date field and the weeknumber field are on the same table so I'm confused on what relationship you are talking about.

[ChrisMendoza]

@mikep7521,

It's a pretty subtle difference that could/can make a pretty huge impact on the expected outcome. I have yet to find a reason to not have a proper 'Date' Dimension table related to my 'Fact' table. The 'Date' table is what I use in the filter context of the visual, not the [Date] or [Weeknum] from the 'Fact' table.

Of course, with my sample, I was not able to produce the wildly incorrect numbers that are in your visual. That is why I asked what was the current filter context of the visual was.

My suggestion is that you create a date table with the necessary information you need e.g. [Week Ending] etc.

Then relate the [Est_Close_Date_c] to the [Date] from the Calendar Table. Use the 'Calendar'[Week Ending] in your visual.

[mikep7521]

I related the [Est_Close_Date_c] to the [Date] from my date Table.  I now have no filters on my table. I'm still receiving the same wrong answers.

[ChrisMendoza]

Share your data if possible please.

[v-piga-msft]

Hi @mikep7521,

It seems that you have solved your problem. Please accept the reply making sense as the solution to your question so that people who may have the same question can get the solution directly.

Best Regards,

Cherry

[ChrisMendoza]

@mikep7521,

Seems like a duplicate post to me. I responded on this post https://community.powerbi.com/t5/Desktop/WEEKNUM-function-isn-t-working/m-p/477951#M222391.

@Greg_Deckler might you agree with my opinion?

[Greg_Deckler]

Yep, duplicate. @mikep7521, please don't cross post, leads to confusion. Tough to be sure, how are the tables related? Which columns are related? Quite possibly has to do with that relationship. 

Overall...Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[mikep7521]

I only have one relationship established. I pulled my two data tables (Deals & Installments) from Salesforce. They are related to each other via ID field on deal table and Deal Name  field on installment table, this relationship was autodetected by power bi. What relationship would I need to establish in order to make this function work correctly.

[Greg_Deckler]

So then what columns from what tables are you using in your table visualization?

[mikep7521]

I'm using estimated close date from the installment table and then trying to create a weeknum column on the installment table as well.

[kcantor]

@mikep7521

Are you creating a dax measure or a dax column? Minor difference but might be causing the issue. 

[mikep7521]

Column. When i attempted a measure i got an error code when adding to a visual.

[Greg_Deckler]

Are those two tables linked in some way? Not sure what you are doing there. Can't you just create a DAX calculated column and use WEEKNUM function?

[mikep7521]

any suggestions as to why my dax function isn't working? 

Close Date Week Number = WEEKNUM(Close Date,2)

[mikep7521]

I only have two tables and yes they are related. I created a column using this formula:

Est. Close Date Week = WEEKNUM(Installment[Est_Close_Date__c],2)

and I'm receiving the same wrong answers as the picture i attached to my original post.