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.

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

  • What are functions for Day Level Intelligence?

PREVIOUSDAY, NEXTDAY

  • How can we get previous day data?
  • If the dates are not continuous then how to get the last day?
  • How can we get last day data in a new calculated column?
  • How can we get last non continuous day in a new calculated column?
  • How can we get same weekday last week and last year?
  • 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

  1. You do not have all dates in calendar table.
  2. 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.
  3. In above case, if you have date with timestamp. Create a new date column and join it with date calendar.
  • How can we add time to a date?
  • 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?

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.

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

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.

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

Amit has 18+ years of experience in Business Intelligence, Analytics, Data Science. He is Chief Analytics Officer at Kanerika & Power BI community Super User

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store