cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
Super User
Super User

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors