topic Re: How to convert a String YYYY-WW to integer YYYY-WW column in DAX Studio? in DAX Commands and Tips

How to convert a String YYYY-WW to integer YYYY-WW column in DAX Studio?

Anonymous — Wed, 22 Dec 2021 20:43:46 GMT

I am trying to import a table the contains last 4 weeks of data. The planning week column is a String column and has the yyyy-ww format. Is there a way to convert this column to an integer YYYY-WW column in DAX Studio and import the last 4 weeks of data?

Note: No date column in the table

Re: How to convert a String YYYY-WW to integer YYYY-WW column in DAX Studio?

bcdobbs — Wed, 22 Dec 2021 21:16:26 GMT

Do you mean DAX Studio?

It's easy to do on data load in Power Query (click transform data):

If I'm missing something can you explain a bit more?

Re: How to convert a String YYYY-WW to integer YYYY-WW column in DAX Studio?

Anonymous — Wed, 22 Dec 2021 21:37:43 GMT

I want to get this data by writing a DAX query in DAX studio rather than in a power query.

I wanna import only the last 4 weeks of data from the DB.

Re: How to convert a String YYYY-WW to integer YYYY-WW column in DAX Studio?

bcdobbs — Wed, 22 Dec 2021 21:45:02 GMT

So you have data in a dataset within powerbi/SSAS and want to a query to return the last four weeks data?

Are you able to create a date table in the model:

Calendar = ADDCOLUMNS ( CALENDAR( DATE (2021, 01, 01), DATE (2021, 12, 13) ), "Year-Week", YEAR([Date]) & "-" & WEEKNUM([Date]) )

You can then join that table to your table using a many many relationship between Year-Week and your planning week. (Set to filter from Calendar to your table).

At that point you can use normal DAX date filtering. Can send example if you need.

If you can't add a date table to the model let me know and I'll think again.

Re: How to convert a String YYYY-WW to integer YYYY-WW column in DAX Studio?

Anonymous — Wed, 22 Dec 2021 22:05:35 GMT

Hi bcdobbs

I imported the WorkOrderLabourS table from the Analysis Services DB by writing the following query.

EVALUATE
FILTER(WorkOrderLabourS, left(WorkOrderLabourS[Planning Week],4)="2020" ||left(WorkOrderLabourS[Planning Week],4)="2021" )

But since the file size is too huge I am looking for importing the last 4 weeks of data from the DB into the power bi by writing a DAX query in Dax studio.

I am looking for something like this,

EVALUATE
FILTER(WorkOrderLabourS, IN LAST 4 CALENDAR WEEKS BASED OF Planning Week COLUMN)

Thank you

Rohini

Re: How to convert a String YYYY-WW to integer YYYY-WW column in DAX Studio?

bcdobbs — Wed, 22 Dec 2021 22:22:19 GMT

Ok I understand now and have given it some thought. You can do the whole thing in DAX studio and create a section of a date table on the fly...

DEFINE TABLE TempCalendar = VAR CurrentDate = TODAY() VAR StartOfThisWeek = CurrentDate - WEEKDAY(CurrentDate, 2) + 1 VAR StartOfPeriod = StartOfThisWeek - 21 RETURN ADDCOLUMNS ( CALENDAR( StartOfPeriod, StartOfThisWeek ), "Year-Week", YEAR([Date]) & "-" & WEEKNUM([Date]) ) EVALUATE CALCULATETABLE ( WeekTable, TREATAS ( VALUES ( TempCalendar[Year-Week] ), WeekTable[Year-Week] ) )

You might have to tweak some of the numbers at the top depending on whether you want to include the current week.

Re: How to convert a String YYYY-WW to integer YYYY-WW column in DAX Studio?

Anonymous — Wed, 22 Dec 2021 22:22:49 GMT

Hi bcdobbs

Thank you so much it worked.

Could please explain to me the logic behind

Filter2 = CurrentYear & "-" & (CurrentWeek - 1)

is it the current week minus 1?

Thank you once again

Re: How to convert a String YYYY-WW to integer YYYY-WW column in DAX Studio?

bcdobbs — Wed, 22 Dec 2021 22:25:17 GMT

See my latest post it's a much cleaner way of doing it that deals with year changes as well.

In answer to my original solution yes it's the current week number take away 1. That works fine until one of your weeks is the start of january.

Re: How to convert a String YYYY-WW to integer YYYY-WW column in DAX Studio?

Anonymous — Mon, 10 Jan 2022 16:59:25 GMT

Hi bcdobbs

I was testing out this code as the new data is available now for the year 2022. However, I get this error with this code.

DEFINE
TABLE
    TempCalendar =

    VAR CurrentDate = TODAY()
    VAR StartOfThisWeek = CurrentDate - WEEKDAY(CurrentDate, 2) + 1
    VAR StartOfPeriod = StartOfThisWeek - 21
    RETURN
    ADDCOLUMNS (
        CALENDAR( StartOfPeriod, StartOfThisWeek ),
        "Year-Week", YEAR(TODAY()) & "-" & WEEKNUM(TODAY())
    )

EVALUATE
    CALCULATETABLE (
        ProjectCost,
        TREATAS ( VALUES ( TempCalendar[Year-Week] ), ProjectCost[Planning Week PC ] )
        )

Error:

Query (18, 22) Table variable 'TempCalendar' cannot be used in current context because a base table is expected.

Could you please help me understand this issue.

Thank You

desai69