Forum Discussion
Direct Query - Calculations
Hi Experts
In Direct Query mode can you do the following calculations and if not is there online evidence to support this
1. Week over Week
2. Year over Year
3. Mont over Month and Sameperiodlastyears
Anonymous , With the Date table, Time intelligence is supported.
if some function is not supported that it will be mentioned on documentation of each function
I discussed time intelligence here for direct query -https://youtu.be/cf-YNWX8Qow?t=2137
Compare import/direct query
https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-about
Also check
Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8
4 Replies
Anonymous , With the Date table, Time intelligence is supported.
if some function is not supported that it will be mentioned on documentation of each function
I discussed time intelligence here for direct query -https://youtu.be/cf-YNWX8Qow?t=2137
Compare import/direct query
https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-about
Also check
Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8Anonymous , You can do that, I shared the video, Where I have shown the use of datesqtd and datesmtd in direct quey mode.
Is there something specific you want
Hi Anonymous ,
You can refer this video which calculated YoY, MoM, WoW in Power BI Desktop:
14-23. How to add YoY QoQ MoM WoW analysis in Power BI Real World Examples TAIK18
When using Time-intelligence functions under Direct Query mode, you have better have a single Date table and need to note that:
These functions are not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.
In addition, if you do not want to use Time-intelligence funcrtions, you can refer this blog which introduces the similar transformation:
Week-based-time-intelligence-in-dax
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
