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. We can see the idea behind the StartString and EndString. 5:15 PM, the value is 43835 17 15 (lower than the first value). If you move slicer to a few hours earlier, e.g. The EndString contains EndDate, EndingHour, and EndingMinute. This means that the StartString increased by 10 (we get the numbers that represent value and are comparable). If we go ten minutes ahead (9:40 AM), the Start string would be 43831 09 40. StartString value, in this case, is equal to 43831 09 30. The start string will contain StartDate, StartingHour, and StartingMinute concatenated together (StartDate = 43831, StartingHour = 09, StartingMinute = 30). These 2 variables will always contain 9 digits that represent timestamp as an integer. 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. They are values that are formatted to always have 2 digits. ![]() Starting and Ending hour variables are simple to understand. For example, the value for the date is 43831. Every date has its integer that represents the date value. With the 8 variables, we want to achieve the combination of numbers that gives us the right number to filter dates. Let’s take a deeper look and examine the DAX code for ValuesBetween measure. The DAX measure we will be using for this scenario: The HoursEnd and MinutesEnd tables are duplicates of Hours and Minutes table. 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 Dates, Hours, and Minutes tables contain only one column which will serve for the filtering purpose. In our data model, we have a fact table that contains data with sales value and timestamp. If you wish to follow along, you can find the. ![]() 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). This means if there was a transaction that occurred on 3rd Jan at 8 AM, it would not be included in our analysis. ![]() 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. 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). 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. However, sometimes this approach is necessary for the sake of the analysis. We rarely need to filter our data by hours and minutes. When filtering data by date, we can simply use a date slicer which is enough in most cases.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |