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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Anonymous
Not applicable

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

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

desai69_0-1640205503011.png

 

2 ACCEPTED SOLUTIONS

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.



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

View solution in original post

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.



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

View solution in original post

8 REPLIES 8
bcdobbs
Community Champion
Community Champion

Do you mean DAX Studio?

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

bcdobbs_0-1640207818769.png

bcdobbs_1-1640207874767.png


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





Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!
Anonymous
Not applicable

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.

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.



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!
Anonymous
Not applicable

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

 

 

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.



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!
Anonymous
Not applicable

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
Not applicable

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

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.



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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