Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mikep7521
Helper III
Helper III

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
mikep7521
Helper III
Helper III

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. 

View solution in original post

11 REPLIES 11
mikep7521
Helper III
Helper III

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. 

v-juanli-msft
Community Support
Community Support

Hi @mikep7521

There is something wrong with your formula, please look at the following test and correct your formula.

Calculated column

weeknum = WEEKNUM([Close Date],2)

8.png

 

Measure

Measure = WEEKNUM(MAX([Close Date]),2)

9.png

 

 

Best Regards

Maggie

 

Anonymous
Not applicable

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

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

Anonymous
Not applicable

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

 

Thanks
Raj

 

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

 

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.

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

It does not need to be a column

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.