Press "Enter" to skip to content

Category: Dates and Numbers

Comparing Non-Standard Time Periods in Power BI

Published 2023-08-01 by Kevin Feasel

Marco Russo and Alberto Ferrari are back in school:

A requirement to apply the following technique is that every day can belong to a term or not, but there are no overlaps between terms. Indeed, if we had overlapping periods, we should create a different solution based on the Comparing different time periods pattern. In the case of school terms, we consider the case of three terms per year, where the first term starts in September of one year, and the last term ends in July of the following year. Therefore, the academic year is identified by two consecutive numbers, such as 2016-2017 (often shortened to 2016-17).

The business requirement is to compare one term with the previous term (within the same academic year or the previous one when we compare the first term of a year) and one term with the same term in the previous year. The goal is to obtain a result similar to the following one.

This turns out to be a little bit of a challenge, though Marco and Alberto have the solution for us.

Comments closed

Filling Gaps in T-SQL

Published 2023-05-30 by Kevin Feasel

Itzik Ben-Gan has a new challenge:

The task involves developing a stored procedure called GetBalances that accepts a parameter called @accountid representing an account ID. The stored procedure should return a result set with all existing dates and balances for the input account, but also gap-filled with the dates of the missing workdays between the existing minimum and maximum dates for the account, along with the last known balance up to that point. The result should be ordered by the date.

My first thought was last observation carried forward, which is now available in SQL Server 2022 (Itzik’s solution 2). I kind of thought of solution 3, though did not think through the mechanics of how it’d work and so I get no credit there.

Comments closed

Common Date and Time Operations in R

Published 2023-05-18 by Kevin Feasel

Steven Sanderson works with dates:

Dates and times are essential components in many programming tasks, and R provides various functions and packages to handle them effectively. In this post, we’ll explore some common operations using both the base R functions and the lubridate package, comparing their simplicity and ease of understanding.

I personally prefer the lubridate style of date operation, but it’s nice to have options.

Comments closed

Elapsed Timers in Powershell

Published 2023-05-16 by Kevin Feasel

Robert Cain keeps track of time:

I’m still working on my documentation for my ArcaneBooks project, but wanted to have something for you to read this week, so decided to show you how to create an elapsed timer in PowerShell.

It can be helpful to determine how long a process runs in PowerShell. You can use it to determine what parts of code may need to be optimized, or gather metrics around your functions.

This is the same stopwatch operation which is available in .NET generally, so if you’re using C# or F#, it’s the same basic process.

Comments closed

Dates and Times in R

Published 2023-05-16 by Kevin Feasel

Steven Sanderson talks dates and times. First up is an overview of how built-in date functionality works in R:

In this post, we will cover the basics of handling dates and times in R using the as.Date, as.POSIXct, and as.POSIXlt functions. We will use the example code below to explain each line in simple terms. Let’s get started!

The second post covers the Date type, as well as date plus time:

In the first part of our code, we use the as.Date() function to find the next Mother’s Day. Since we don’t need to consider specific times, we can simply use the Date class, which simplifies the process. We create a vector with two elements: startMothersDay and endMothersDay, both set to “2024-05-14”. This represents the range of Mother’s Day for the year 2024. Finally, we store the result in the variable NextMothersDay and print it to the console. Voilà! We have the next Mother’s Day date.

Note that this isn’t a post about date calculation, such as finding the Nth Sunday in the month of May

Comments closed

Adding a UTC Time Zone Indicator to a Date in SQL Server

Published 2023-05-04 by Kevin Feasel

Bill Fellows fights with the language:

It seems so easy, I was building json in SQL Server and the date format for the API specified it needed to have 3 millsecond digits and the zulu timezone signifier. Easy peasy, lemon squeezey, that is ISO8601 with time zone Z format code 127

SELECT CONVERT(char(24), GETDATE(), 127) AS waitAMinute; Running that query yields something like 2023-05-02T10:47:18.850 Almost there but where’s my Z? Hmmm, maybe it’s because I need to put this into UTC? SELECT CONVERT(char(24), GETUTCDATE(), 127) AS SwingAndAMiss;

Running that query yields something like 2023-05-02T15:47:18.850 It’s in UTC but still no timezone indicator.

Read on for several attempts and what finally did the trick.

Comments closed

Rounding Errors by Data Type in DAX

Published 2023-04-25 by Kevin Feasel

Marco Russo and Alberto Ferrari shave of fractions of a cent:

The first reason to choose a data type is the range of numbers supported and the precision. However, the result of a mathematical operation may produce a number that cannot be represented in the chosen data type, which requires a rounding operation. Therefore, the result of one same sequence of operations can produce different results depending on the data type and the order of execution. In this article, we discuss the typical rounding behavior for each data type and how to avoid possible issues in your DAX formulas because of any differences from the results you may have expected.

Read on to learn what granularity limits exist for integers, fixed decimal numbers, and floating point operations.

Comments closed

Building a Time Dimension in Power BI

Published 2023-04-07 by Kevin Feasel

Allison Kennedy shares a variant of DimTime:

 Today’s post is going to be short and sweet. I’m simply publishing my version of a DimTime table for Power BI.

Thanks to Radacad for posting a great article on why we need a DimTime table, along with their script for creating one in Power BI. 

If you don’t already have a DimDate table, you can find my version in my DimDate: What, Why and How blog article. I update the script occasionally with new requests. 

Click through for the script.

Comments closed