How to show the current week number

[icturion]

hi,

I have a data set with past, present and future data. Now I want to display the totals per week number in a chart. where the current week in the x-axis is stated as current.

as an example x-axis; 39 40 current 42 43

I already have a calendar table with the correct week numbers

hopefully someone knows a solutions.

thx

[NBSheff]

Thanks for this. When using:

Current Week = IF(Date_Table[Week_Number] = WEEKNUM(TODAY(),2), "Current", CONVERT(Date_Table[Week_Number],STRING))

I get the error:

"A single value for column 'Week_Number' in table 'Date_Table' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

Any help appreciated.

[mphillenga]

@PaulDBrown @icturion 

Thanks for this great solution.

However, due to it being a string I'm having problems to sort the week in the right order.

I would like to have the current week at the far right of the chart and all weeks before left of it.

How have you managed to sort this properly?

Thanks

[PaulDBrown]

@mphillenga 

Are you creating a calculated column for the weeks? If so, can you show the structure of the table? You should be able to add a new column to sort the week column using SWITCH. Something along the lines of:

Week sorting order =
SWITCH ( TRUE (), Table[Current week] = "Current", 100, Table[WeekNumber] )

You should then be able to use this column to sort the Current week column in the table, which will then sort accordingly in visuals.

[mphillenga]

Thanks @PaulDBrown  this works 👍

[PaulDBrown]

Can you show a depiction of the expected outcome? The DAX is simple, as in:

Current week = WEEKNUM(TODAY())

The question is how you wsih to protray this in the visual

[icturion]

Hello PaulDBrown,

thank you for the quick respons. i use a calculated column with this dax formule

Current Week = IF(Kalender[WeekNumber] = WEEKNUM(TODAY()), "Current", CONVERT(Kalender[WeekNumber],STRING))

But the current week out come is wrong, i now return week 42 as current, but the correct week is 41

do you know how to resolve this? 

[PaulDBrown]

If you use a calculated column, the data will only be updated on refresh. A measure will be refreshed whenever the measure is used.

You also need to be aware of how the week numbers are counted. The expression WEEKNUM(TODAY()) will count weeks based on:
"The week containing January 1 is the first week of the year and is numbered week 1."

The expression WEEKNUM (TODAY(), 2) will count weeks based on:
"The week containing the first Thursday of the year is the first week of the year and is numbered as week 1. This system is the methodology specified in ISO 8601, which is commonly known as the European week numbering system."

Refer to

https://docs.microsoft.com/en-us/dax/weeknum-function-dax 

[icturion · Accepted solution]

The problem is resolved.

this is the solution

Current Week = IF(Kalender[WeekNumber] = WEEKNUM(TODAY(),21), "Current", CONVERT(Kalender[WeekNumber],STRING))

thanks Paul DBrown for helping