Forum Discussion
Helper IIIDate 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
Helper IIIHi 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))
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.
Thanks,
Lydia Zhang
- Jolyon
thank you,Lidya!
I will check it!
- Jolyon
Hallo Anonymous
could you please send the attached pbix.file from your message to my Email [email protected]?
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!
Hi Jolyon,
I have sent the PBIX file to you via Email. Please check the formulas in the PBIX file.
Thanks,
Lydia Zhang