Power BI — Day Intelligence Questions — Time Intelligence 5–5

In this blog, we will try to answer Top 10 Frequently Asked Questions on Day Level Intelligence in Power BI.

3 min readJul 15, 2020

Pre-requisites:

For any Time Intelligence, you must have a date table. Please refer the below links to create one date table.

Make sure your calendar has the following fields.(For a financial year, which is not same as the calendar year, refer the Power BI community blog for some of these calculations.).

Date = CALENDAR(date(2015,03,01),Date(2021,02,28)) //calendar
Is Today = if('Date'[Date]=TODAY(),"Today",[Date]&"")

What are functions for Day Level Intelligence?

PREVIOUSDAY, NEXTDAY

How can we get previous day data?

Previous Day Sales = CALCULATE([Sales],PREVIOUSDAY('Date'[Date]))
Day behind Sales = CALCULATE([Sales],dateadd('Date'[Date],-1,Day))
Last Day = CALCULATE([Sales], FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])-1))

If the dates are not continuous then how to get the last day?

Last Day Non Continuous = CALCULATE([sales],filter(ALLSELECTED(‘Date’),’Date’[Date] =MAXX(FILTER(ALLSELECTED(‘Date’),’Date’[Date]<max(‘Date’[Date])),’Date’[Date])))

How can we get last day data in a new calculated column?

Last Sales Date = maxx(FILTER(Sales1,Sales1[Sales Date] =EARLIER(Sales1[Sales Date])-1),Sales1[Sales Date])

How can we get last non continuous day in a new calculated column?

Last Non Cont Sales Date = var _max = maxx(FILTER(Sales1,Sales1[Sales Date] < EARLIER(Sales1[Sales Date])),Sales1[Sales Date])
return maxx(FILTER(Sales1,Sales1[Sales Date] =_max),Sales1[Qty])

How can we get same weekday last week and last year?

Same Week Day Last Week = CALCULATE([Sales], dateadd('Date'[Date],-7,Day))Same Week Day Last year = CALCULATE([Sales], dateadd('Date'[Date],-364,Day))

After joining the date with the Date table, we are facing certain issues i.e. not all dates are displaying and we are getting null values. Why is it happening and how it can be resolved?

There can be a few reasons

You do not have all dates in calendar table.
You may have a timestamp in your date and it is not visible because of the datatype date or the format that does not include time. Change the datatype to datetime and have a format that shows time to check.
In above case, if you have date with timestamp. Create a new date column and join it with date calendar.

Sales Date 1= [Sales Date].Date
Sales Date 2= Date(Year([Sales Date]), Month([Sales Date]), Day([Sales Date]))

How can we add time to a date?

Sales Time = [Sales Date] + time(3,12,1)// it is in 24-Hr format

We are not able use dateadd or startofmonth with today() or any other static date. How can we add or subtract days in today’s date or find month start date for today?

For Day we can simply Add Days to Date Or can Use Date as Date add
7 Days Ahead = TODAY()+7
Current Month Start = var _max =TODAY() return DATE(YEAR(_max),MONTH(_max),1)

Refer to this blog, how to use date function as dateadd function:

How can we default a page to today or yesterday?

There is no option to default a slicer on using a function. The work around is to create a column given below and select today in the slicer.

Is Today = if(‘Date’[Date]=TODAY(),”Today”,[Date]&””)

Sort this column on date column. Or you can have a column give below, you can filter on yesterday too.

Date Type = SWITCH(TRUE(),’Date’[Date]=TODAY(),”Today”,’Date’[Date]=TODAY()-1,”Yesterday”,’Date’[Date]&””)

Let us know what you think about the above suggested methods.
Share the scenario where it helped you. Please find the file, for you to explore.
Also, would love to hear your approach on the alternate methods of using above Time Intelligence functions.

Want to Learn More, Explore these videos and video series.

Complete Power BI Tutorial/Course in Just 11 Hours

Mastering Microsoft Fabric: 30 Videos

Want to Learn SQL: 20 Videos

Mastering Power BI: 230+ Videos

Power BI Concepts- How Power BI Works- 150+ Videos

Migrating from Tableau to Power BI: 30 Videos

DAX for SQL Users: 10 Videos

What works in Power BI Direct Query- 10+ Videos

सीखे Power BI हिंदी में- 60+ Videos

Power BI Hindi On Demand- 10+ Videos

About the Author: Amit Chandak

Amit Chandak is Chief Analytics Officer at Kanerika Software Private Limited where the priority is to get the most value out of the customers’ data. He is also an avid Power BI Super User due to his contributions to the Power BI community.

Power BI is turning 5! In celebration of this achievement, Amit is sharing 5 blog posts on the community, Series: Power BI turns 5. You can find the links in this post.

If there is any other topic in your mind, do not hesitate to reach out to Amit. He is looking for different talking points to share his thoughts on Power BI.

You can follow his work on LinkedIn, Twitter, Medium, Youtube