Date Transformations in Direct Query mode?

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...

Need Help

Anonymous
9 years ago
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/td-p/44900

Thanks,
Lydia Zhang

Anonymous

Not applicable

9 years ago

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