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

[amitchandak]

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.

Data Loading:

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

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.

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]) 

Join it with the sales table:

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)) )

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