Forum Discussion
Date Transformations in Direct Query mode?
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]) – 12. 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.
Thanks,
Lydia Zhang
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
Hi, Anonymous,
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!
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 ZhangHi Anonymous
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))
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.
Hi Lydia Zhang:
Same question, how to transform to week of year format?
Thanks for answer.
