Date Transformations in Direct Query mode?

[Jolyon]

Hello,

I have been trying "Direct Query" to our sql server tables all on 1 database, and it seems to work fine.

But now I bumped on limitations of Direct Query, when I tried to make transformations with Date. As I have understood, special treatment of date columns (year, quarter, month, day, so on) are still not supported in DirectQuery mode.

What can I do in this case?

I have a date "01.01.2015 00:00:00", but I need also columns with

• MonthYear
• Month(name)
• Year

for example: Jan 2015; January; 2015

(actually I need also get rid of "00:00:00", but Direct Query prohibits this transformation as well)

Another question is, is it possible in Power BI Desktop - Direct Query Modus to work with calculated columns and measures, and DAX at all?

[v-yuezhe-msft]

Hi @Jolyon,

You can use the following formulas to transform your date columns, also check the example in the screenshot below.

Year = YEAR('Sales SalesOrderDetail'[ModifiedDate])

Month = SWITCH(MONTH('Sales SalesOrderDetail'[ModifiedDate]),1,"Jan",2,"Feb",5,"May",6,"Jun",7,"Jul")

Yearmonth = CONCATENATE('Sales SalesOrderDetail'[Month], 'Sales SalesOrderDetail'[Year])

MonthName = SWITCH(MONTH('Sales SalesOrderDetail'[ModifiedDate]),1,"January",2,"Febuary",5,"May",6,"June",7,"July")




In addition, please click the date column in the Fields panel  in the following screenshots, then you can click on Modeling in the Desktop ribbon and change data type of the column to Date and change date format to your desired format.



Thanks,
Lydia Zhang

[FrankWeng]

Hi Lydia Zhang:

Same question, how to transform to week of year format?

Thanks for answer.

[Jolyon]

Hi, @v-yuezhe-msft,

Thanks a lot for the answer! This could be a solution.

I have one more question: can I give for this formula only up to 10 Values? Because when I tried to give all the 12 Months in the formula like:

Month = SWITCH(MONTH('Table1'[Modified_date]); 1;"Jan"; 02;"Feb";3;"März";4;"April"; 5;"May";6;"Jun";7;"Jul";8;"August";9;"September";10;"Oktober";11;"November";12;"December")

I got an error message:

 

Another general question: as I connected through DirectQuery-->SQL Server-->to our CRM database,

I have got the views only for Reports and Relations, but no Datasets:

 

Is the only possible way to create measures and new columns(with formula) in this case - just create it directly in Reports view?

If I go to Query Edit, I find there only customized column, but no Measures or Columns.

Thanks a lot!

[v-yuezhe-msft]

 @Jolyon,

Use the following formula instead.

MonthName= IF(MONTH('Sales SalesOrderDetail'[ModifiedDate])<6,SWITCH(MONTH('Sales SalesOrderDetail'[ModifiedDate]),1, "January", 2, "February", 3, "March", 4, "April" , 5, "May"),SWITCH(MONTH('Sales SalesOrderDetail'[ModifiedDate]),6, "June", 7, "July", 8, "August"
, 9, "September", 10, "October", 11, "November", 12, "December"
, "Unknown month number"))

Thanks,
Lydia Zhang

[Jolyon]

Hi @v-yuezhe-msft
Thank you for the answer, it solved a part of the problem!

I have one more question about Date Transformation:

If I need to show  the OpportunitiesAmount from NOW till next 6 Months(or say 180 days), how could I do that? is it possible to create such a Variable, that would define, what Date is it today and take also next 180 days?

("NOW" implying the day, when I open and refresh my BI Report(which uses DirectQuery))

[v-yuezhe-msft · Accepted solution]

Hi @Jolyon,

In your scenario, please follow the steps below to create a column in Date table and use the column in visual filter.

1. Create a Column called MonthSequentialNumber in the date table using the following formula.
MonthSequentialNumber = MonthSequentialNumber = year('Date'[Date]) * 12 + Month( 'Date'[Date]) – 1

2. Create a measure called CurrentMonthSeqeuntialNumber = Year(Today()) * 12 + Month(today()) - 1

3. Create a column in Date table called show using the fomula below

show = IF('Date'[MonthSequentialNumber]>=[CurrentMonthSeqeuntialNumber]&& 'Date'[MonthSequentialNumber]<=[CurrentMonthSeqeuntialNumber]+5,1,0)

4. Show column will have a value of 0 or 1 in the date table.

5. Create relationship using Date columns in Date table and your target table.

6. Create a column chart where Axis is Date and Value is OpportunitiesAmount, then drag Show column to visual filter, there is an example for your reference, for more details, please review the example in this attached PBIX file.

 
Reference:
http://community.powerbi.com/t5/Desktop/Filter-Dates-to-only-show-current-month-and-next-12-months/t...

Thanks,
Lydia Zhang

[Jolyon]

Hallo @v-yuezhe-msft

could you please send the attached pbix.file from your message to my Email jolyonforsyte@yandex.com?

For I didn't manage to register myself in OneDrive.

And I have one more question:

when I take formula

show = IF('Date'[MonthSequentialNumber]>=[CurrentMonthSeqeuntialNumber]&& 'Date'[MonthSequentialNumber]<=[CurrentMonthSeqeuntialNumber]+5,1,0)

should I place a comma between  5 and 1 and 0 or semicolon?

In my Power BI Desktop I have always this issue with semicolons - if I take a comma, it is marked as a mistake in the formula.

P.s.I have the newest Updateof Power BI

thanks a lot!

[v-yuezhe-msft]

Hi @Jolyon,

I have sent the PBIX file to you via Email. Please check the formulas in the PBIX file.

Thanks,
Lydia Zhang

[Jolyon]

thank you,Lidya!

I will check it!

[Greg_Deckler]

Try nested IF instead of SWITCH.

Yes, when using Direct Query, then the only way to create measures and calculated columns is in Report View.

[Greg_Deckler]

Yes, you can work with calculated columns and measures with Direct Query now. I would recommend turning on File | Options and settings | Options | Direct Query | Allow unrestricted measures in Direct Query mode.

Here is the latest documentation on what works with Direct Query (although this is for SQL Server 2016 so your mileage may vary):

https://msdn.microsoft.com/en-us/library/mt723603.aspx

Although, now that I look at this, these are about analysis services and not database services so now I'm going to have to test and make sure this stuff still holds.

[Greg_Deckler]

OK, I did confirm that in Direct Query mode against a database, I can add custom columns in M, calculated columns in Desktop and measures in Desktop.

[Jolyon]

Hi @Greg_Deckler,
thanks for the answer! I did as you told.
But still I can do only limited number of transformations with date.
E.g. I have originally date 01.12.2012.

I can transform it now to Year-> 2012 (I just make a duplicate of column with date "01.12.2012" and then chose Transform-->Year.

But I can not transform it to Month --> December.

I get the error message"This transformation is not supported by Direct Query mode".

Do you have any other idea, what could I do?

If this helps, a bit of information about using Power BI: I want to make some reports basing on data from our CRM System.

So we connected from Power BI Desktop (DirectQuery) through SQL Server to our CRM database.

Is there probably more exquisite way to get data and make reports?