Including hours and minutes in DAX date filters

Krešimir Ledinski

Krešimir Ledinski

Microsoft certified expert in the field of Business Intelligence. His biggest passions are DAX, M, and data modeling.

When filtering data by date, we can simply use a date slicer which is enough in most cases. We rarely need to filter our data by hours and minutes. However, sometimes this approach is necessary for the sake of the analysis.


In this blog, we will examine the scenario where we need to include hours and minutes of the day in our filtering conditions, but only for the first and the last day. For example, if we want to observe the period between 1st of January and 5th of January, from 9:30 AM (1st Jan) to 8:15 PM (5th Jan), we want to take into consideration the complete period between (full 24 hours for 2nd, 3rd and 4th Jan) and only relevant hours for the 1st Jan (from 9:30 AM to the end of the day) and the 5th Jan (from 12 AM to 8:15 PM). If we were to apply a simple filter that includes dates from 1st to 5th, and time from 9:30 AM to 8:15 PM we would lose all the data from the middle 3 days that are not in the filtered hours period. This means if there was a transaction that occurred on 3rd Jan at 8 AM, it would not be included in our analysis.

To avoid this, we will provide the solution by using DAX with a measure that will filter hours only from the relevant days (first and last one). If you wish to follow along, you can find the .pbix file on this link.

The scenario

In our data model, we have a fact table that contains data with sales value and timestamp.


The Dates, Hours, and Minutes tables contain only one column which will serve for the filtering purpose. The Dates table contains all necessary dates, the Hours table contains 24 rows with values from 0-23, and the Minutes table contains 60 rows with values 0 to 59.

The HoursEnd and MinutesEnd tables are duplicates of Hours and Minutes table.

The solution


The DAX measure we will be using for this scenario:


Let’s take a deeper look and examine the DAX code for ValuesBetween measure.

With the 8 variables, we want to achieve the combination of numbers that gives us the right number to filter dates. Every date has its integer that represents the date value. For example, the value for the date 1/1/2020 is 43831. The value for the 1/5/2020 is 43835.

Starting and Ending hour variables are simple to understand. They are values that are formatted to always have 2 digits. For example, 4 AM is 04, and 4 PM is 16. The same logic applies for minutes.

Now, after we examined the first six variables, we can take a closer look at the StartString and EndString variables that are later used as a filter condition in our measure.

These 2 variables will always contain 9 digits that represent timestamp as an integer. For example, we will take 1/1/2020, 9:30 AM. The start string will contain StartDate, StartingHour, and StartingMinute concatenated together (StartDate = 43831, StartingHour = 09, StartingMinute = 30). StartString value, in this case, is equal to 438310930. If we go ten minutes ahead (9:40 AM), the Start string would be 438310940. This means that the StartString increased by 10 (we get the numbers that represent value and are comparable).

The EndString contains EndDate, EndingHour, and EndingMinute. For the date 1/5/2020, 8:15 PM is 438352015. If you move slicer to a few hours earlier, e.g. 5:15 PM, the value is 438351715 (lower than the first value).

We can see the idea behind the StartString and EndString. We use variables to manipulate with date, time, and hour values to get integers which are later used to compare different dates and time values.

The final point of this code is using SUMX to get the total value for the specified period of time. In SUMX function, we filter the fact table in the way that for each row, we concatenate date value, hour, and minute (based on the same logic that we applied for the StartString and EndString variables), and filter it based on conditions stated earlier in variables. This way, only the rows that are between two integers (in our earlier example, all integers between 438310930 and 438352015) pass the filtering condition.

The final test

Now let’s examine this on another example in Power BI to see if our measure works correctly.


In the table on the right, we have TotalValue that is a sum of values that take into account only date filters (without time filters), ValuesBetween, and Diff (TotalValue – ValuesBetween). On the left side, we are using 5 slicers to filter, one for date and two for hours and minutes to select the period of observation. Hours (from) and Minutes (from) slicers are coming from Hours and Minutes tables, and Hours (to) and Minutes (to) come from HoursEnd and Minutes End tables.

We can see that the values for the first and the last date for these two measures are different, and the values in the dates between are identical. This means that our code works well, and it provides a way to filter only marginal dates in our analysis.

Be careful when using this technique with big tables. Since there is a high materialization happening at a query time, you can expect degraded performance on large tables. We could further optimize this calculation by using SUMMARIZE() function instead of a whole table as a first SUMX() argument, but that is another topic about optimization.

Also, this technique does not work with built-in time intelligence functions such as DATESYTD, SAMEPERIODLASTYEAR, DATEADD, etc.

Hope you find this solution helpful in your work!


In case you have any comments, please post them below!

Table of Content

Table of Content

LEARN POWER QUERY

Improve your PowerQuery skills with
Exceed Academy

COMMENTS

Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments

GET LATEST BI NEWS TO YOUR INBOX

Receive the latest updates on all business analyst news across all platforms.

By subscribing you are agreeing to our Privacy Policy.

Related blog posts

Dynamic Coalesce in PowerQuery

We can define coalesce in PowerQuery as the last value existing in a single row of multiple columns positioned from left to right. In our example that would be the last value from the month columns. In this article, we will show you how to create both static and dynamic...

Read more

Dynamic bulk insert to Power Apps collection

In one of our latest blog posts, we described a technique used to copy tables from other programs (e.g. Excel) into PowerApps collections. This solution had a drawback. Within the code, the column order was referenced to determine which column to collect. This means that, for the first column in...

Read more

Bulk insert to Power Apps collection

In this blog, we will show you the technique used to collect tables for your app by using the bulk insert. We will show you how to make the same table structure in Power Apps as Excel tables. We will use Excel to show you how to copy-paste tabular data...

Read more

Why should you start using Power Apps?

In this article, we will take a look at what is Power Apps, why should we use it, and when organizations can benefit from custom apps created in the Power Apps platform.Many organizations are not yet familiarized with Power Apps capabilities, and the ways it can improve their agility, modernize and automate manual processes.   What is Power...

Read more
0
Would love your thoughts, please comment.x
()
x
Scroll to Top