How to make Year and Week as a Date with Direct Query

[Anonymous]

Hey everyone,

I have a requirement where users want to see a KPI on a line chart with the axis of WEEKNUM & YEAR.

I'm bringing in a value called "Week Year" where im concattenating year and week together in a sql query.

concat( concat(year(date),'-'), week(date)) as "WeekYear".

So you would see each year with 1 through 52/53 weeks.

Values basically look like:

2020-01 - > 1st week of 2020

2020-02 -> 2nd week of 2020

2020-52 -> 52nd week of 2020

Power BI reads it as a Text. But I need a way to plot this on the x axis. FORMAT does not work since im using SAP HANA Direct query. 

Anyone have a solution?

[v-xuding-msft]

Hi @Anonymous ,

May I ask why you don't use the original date column in data source on x-axis?  Time intelligence capabilities are unavailable in DirectQuery. For example, special treatment of date columns (such as year, quarter, month, or day) isn't supported in DirectQuery mode. So if you create a date column based on your "WEEKNUM & YEAR",  the report connection mode will be changed to mixed (Direct query + import). 

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-use-directquery#limitations-of-directquery

If the problem is caused by sorting "WEEKNUM & YEAR" order,  maybe you could get some ideas from this video: https://www.youtube.com/watch?v=n_gnuFS8qoY&feature=youtu.be.

[amitchandak]

@Anonymous , while direct query has limitation what can work

You can create date from a week like

Week to Date = var _st = date(left([Week],4),1,1)
var _week = right([Week],2)*1
Return _st+((_week-1)*7) -WEEKDAY(_st,2)+1

In your case 

Week to Date = var _st = date([year],4),1,1)
var _week = [Week]
Return _st+((_week-1)*7) -WEEKDAY(_st,2)+1

refer the blog.: https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3

you can have date/week table in power bi (mix mode) and use that to deal with week

refer this : https://youtu.be/24arfrD3Qzk

[Greg_Deckler]

@Anonymous - Can you just keep them separate and use drill down? Otherwise, can you just concatenate them with "" instead of "-"?