cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mikep7521 Member
Member

Week Number for a certain date

I'm attempting to create a column/measure that captures what weeknum a certain date is in but i'm not receiving the correct answer. Below is my column and measure formula:

Column

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

Measure

Est. Close Date WeekNum = WEEKNUM(SUM(Close Date]),2)

 

Why am I not receiving the correct answer?

1 ACCEPTED SOLUTION

Accepted Solutions
mikep7521 Member
Member

Re: Week Number for a certain date

I figured out the issue. There were multiple installments that had the same closing date whcih is what was leading to the wrong values being displayed. 

11 REPLIES 11
Super User
Super User

Re: Week Number for a certain date

Perhaps the "." after the table name cause the issue, also enclose the table name in single quote as your table namehas space in it.Try the below one.

 

Column

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

 

Thanks

Raj

mikep7521 Member
Member

Re: Week Number for a certain date

The I recieve an error code: The syntax for '[Date]' is incorrect. (DAX(WEEKNUM(Installment[Close_Date][Date],2))).

Super User
Super User

Re: Week Number for a certain date

Your formula is not correct. Can you share the snapshot of your formula? Or Share the exact formula you used.

 

Thanks
Raj

 

mikep7521 Member
Member

Re: Week Number for a certain date

I know, thats why I'm posting on the forum. I shared the formulas above, those are the two formulas I've tried.

mikep7521 Member
Member

Re: Week Number for a certain date

Below you will see the formula I used, as well as the output of that formula. The very first row with the date of 1/15/18 should be week number 3 but I'm receiving 12. why is this?

Capture PowerBI.PNG

 

RMDNA Senior Member
Senior Member

Re: Week Number for a certain date

Hi @mikep7521,

 

Can you do it in the back end, or does it need to be a measure? I would just use "Column from Example" in Query Editor; it should pick up the pattern after a few entries.

mikep7521 Member
Member

Re: Week Number for a certain date

Could you please elaborate? I'm not familiar with what you are referencing.

mikep7521 Member
Member

Re: Week Number for a certain date

It does not need to be a column

mikep7521 Member
Member

Re: Week Number for a certain date

I tried that route and I'm still receiving outputs that are wrong.