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

[Anonymous]

Hi

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

[bcdobbs · Accepted solution]

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.

[bcdobbs]

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?

[Anonymous]

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.

[bcdobbs · Accepted solution]

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.

[Anonymous]

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

[bcdobbs · Accepted solution]

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.

[Anonymous]

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

[Anonymous]

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

[bcdobbs]

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.