Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started


Decoding DirectQuery in Power BI Part 5: Time Intelligence with Date Table in Power BI

Problem description:

Can we use a date table in Power BI for getting time intelligence, when a DirectQuery database does not have a date table?



I installed SQL Server Express edition on my machine and uploaded four tables - customer, geography, item, and sales.

Screenshot 2020-08-02 18.48.12.png


Data Loading:

Connected to SQL server in DirectQuery mode and edited all these 4 tables.

Screenshot 2020-08-02 18.51.47.png


We created the required joins between Sales and Customer, Sales and Geography, and Sales and Item. All joins have one-to-many relationships from the dimension table to the fact table.


Screenshot 2020-08-02 18.36.17.png


Date Table in Power BI

Create a date table in Power BI, as the data source does not have a date table.



Date = CALENDAR(date(2017,01,01),date(2020,12,31)) 
Month Year = FORMAT([Date],"MMM-YYYY")
Month Year Sort = FORMAT([Date],"YYYYMM")
Year = YEAR([Date]) 





Screenshot 2020-08-11 13.17.33.png


Join it with the sales table:


Screenshot 2020-08-11 13.17.50.png


Create Time Intelligence formulas:




MTD = CALCULATE([Gross Sales M],DATESMTD('Date'[Date]) )
QTD = CALCULATE([Gross Sales M],DATESQTD('Date'[Date]))
YTD = CALCULATE([Gross Sales M],DATESYTD('Date'[Date])) 
LYTD = CALCULATE([Gross Sales M],DATESYTD(DATEADD('Date'[Date],-1,YEAR)) )




Screenshot 2020-08-11 13.32.05.png


So, the conclusion is that we can use a date table created in Power BI for time intelligence.


Do share your experience with DirectQuery and let us know if you want to check out something different in DirectQuery mode.


You can get all my posts at