Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Did you hear? There's a new SQL AI Developer certification (DP-800). Start preparing now and be one of the first to get certified. Register now

amitchandak
Super User
‎08-17-2020 10:47 AM

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?

 

Setup:

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])) 
LMTD = CALCULATE([Gross Sales M],DATESMTD(DATEADD('Date'[Date],-1,MONTH) ))
LQTD = CALCULATE([Gross Sales M],DATESQTD(DATEADD('Date'[Date],-1,QUARTER)))
LYTD = CALCULATE([Gross Sales M],DATESYTD(DATEADD('Date'[Date],-1,YEAR)) )

 

 

 

Screenshot 2020-08-11 13.32.05.png

Conclusion

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 https://community.powerbi.com/t5/Data-Stories-Gallery/Blog-Analysis/m-p/1265567#M4403