Press "Enter" to skip to content

Category: Dates and Numbers

Calculating Business Hours with DAX

Published 2020-06-25 by Kevin Feasel

Matt Allington combines DAX and a calendar table to calculate business hours:

I was helping a client this past week to calculate the total business hours between a start date/time and an end date/time, taking into account the working days, public holidays and non-working weekends, etc.  As is often the case, I thought it would be a great blog article that I could share with my readers.  This is a pretty involved problem with lots of moving parts, so as such I have decided to record a video showing you how I solved the problem, 1 step at a time.

Click through for the video as well as a description and the code.

Comments closed

Creating Currency Formatting Strings with Power BI

Published 2020-05-06 by Kevin Feasel

Gilbert Quevauvilliers walks us through formatting currencies via calculation groups in Power BI and Analysis services:

When I first started looking at the calculation groups and changing the currency formats, I thought that my existing currency format was correct. Boy was I wrong and once I found that out and corrected it, my Currency Format Strings started working.

As per the Microsoft documentation found here Dynamic format strings for currency conversion I had to make sure that my Currency format followed the following pattern.

Read on for an example and demonstration.

Comments closed

Date and Time Storage in SQL Server

Published 2020-04-23 by Kevin Feasel

Randolph West covers the internals of how date and time data types are stored in SQL Server:

DATE is the byte-reversed number of days since the year 0001-01-01, stored as three bytes. It goes up to 9999-12-31, which is stored as 0xDAB937. You can check this value by reversing the bytes and sticking them into a hex calculator. 37 B9 DA equals 3,652,058, which is the number of days since 0001-01-01.

If you try to cast 0xDBB937 as a DATE value (by incrementing the least significant bit DA by 1), it will throw a conversion error. There is obviously some overflow detection that protects against corruption in a date type.

Randolph looks at DATE, TIME, DATETIME(2), and DATETIME and explains how each is storedon a page.

Comments closed

Adding Time Zone-Adjusted Report Execution Times

Published 2020-04-20 by Kevin Feasel

Brett Powell shows how you can display a report’s execution time in a particular time zone:

For reports being viewed by users around the world, simply modifying the footer text box expression to note that this time is UTC may be a sufficient. However, for many paginated reports the users are all in one time zone and some of these users may ask to have the time zone conversion handled within the BI solution. The example in this post targets this scenario.

Even if the report serves users in multiple time zones, it’s technically feasible to leverage the UserID global field and a simple user to time zone mapping table to provide a local report execution time to all users. However, I tend to think most projects would not want to commit the time/resources for this logic – UTC date/time is what the users would get.

If you do need local report execution time, Brett has you covered.

Comments closed

Finding Active Users by Date with DAX

Published 2020-03-16 by Kevin Feasel

Reza Rad takes us through the subscription pattern:

One of the common patterns of a data model is the subscription pattern. In this pattern, we have subscriptions (or tickets, or issues, or whatever else you want to call it) open date and close date. The way that this data is stored in a table makes it a bit challenging to get informative insight out of it. In this post (first of the series), I am going to explain about the subscription pattern and one of the common calculations needed for it; which is active subscribers at any given date, or open tickets at any given date.

In case you’re curious, here’s a solution which works in T-SQL. I’ve really taken to event-style tables, where there’s one row per state change, so instead of having a begin date and an end date for each action, have a row which contains the date and the type of action. This makes operating on the data a lot easier, though it does make rules preventing common entry problems (end date before start date, etc.) a bit trickier.

Comments closed

Currency Conversion in Power BI

Published 2020-03-09 by Kevin Feasel

Marco Russo takes us through some tips when performing currency conversions using Power BI:

The rule of thumb is to apply the currency exchange conversion upfront. Therefore, it is a good idea to solve all the scenarios requiring a single currency in the report by converting all amounts at the time of data import into the data model. When you need to create a report in multiple currencies, computing data in advance could be challenging and expensive. Therefore, a dynamic solution based on DAX measures and a properly designed data model makes more sense.

In this article, we only consider the third scenario, “Data in a single currency, report with multiple currencies”. The second scenario could be implemented by transforming data so that it is imported in the model in a single currency, moving the challenge over to the very scenario we describe in this article. An extended description of the three scenarios with other dynamic solutions is included in a chapter of the Analyzing Data with Microsoft Power BI and Power Pivot for Excel book.

This is quite a useful article if you work with multiple currencies.

Comments closed

Fun with Filtering Between Start and End Dates

Published 2020-02-19 by Kevin Feasel

Brent Ozar shows why the StartDate + EndDate pattern is not great for filtering:

If all you need to do is look up the memberships for a specific UserId, and you know the UserId, then it’s a piece of cake. You put a nonclustered index on UserId, and call it a day.

But what if you frequently need to pull all of the memberships that were active on a specific date? That’s where performance tuning gets hard: when you don’t know the UserId, and even worse, you can’t predict the date/time you’re looking up, or if it’s always Right Now.

This is where I advocate pivoting to a series of event records, so instead of a start date and end date, you have an event type (started, expired, cancelled, etc.) and a date. There are other alternatives as well, but it’s a good thought exercise.

Comments closed

SQL Server Truncating Numbers to Asterisks

Published 2019-12-20 by Kevin Feasel

Bert Wagner points out that some numeric types handle overflow in a weird way:

Why does SQL Server sometimes error when converting a number into a string, but other times succeeds and returns an asterisk?

I don’t know.

The best (and logical) answer I could find online is from Robert Sheldon, who attributes it to poor error handling practices, “…before error handling got a more reputable foothold.”

This makes it important to check your results. I imagine that there’s somebody who relies upon this exact functionality, but it’s pretty weird.

Comments closed

TRY_PARSE and NaN

Published 2019-12-20 by Kevin Feasel

Slava Murygin finds a nasty bug in SQL Server:

Database in trouble has a table with FLOAT column. It’s Front-End application verifies user’s input and inserts the data into that column using TRY_PARSE function.
The developer’s intention was that any “Not-a-Numeric” or “Out-of-Range” values will be automatically converted to NULL and it will be for user’s discretion to verify and fix these values.

However, one of the application users was very educated and instead of empty space, NULL or any other bad not numeric value the user supplied data with value of “NaN” for empty cells, which simply stands for “Not a Numeric”.
That action caused a database corruption!

Click through for a demo which you should not repeat on a work server.

Comments closed