Solved: Date Transformations in Direct Query mode? - Page 2

Jolyon · ‎10-07-2016

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 · ‎10-17-2016

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Date Transformations in Direct Query mode?

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024