A Fine Slice Of SQL Server
Koen Verbeeck takes us through some unexpected results with the SUMX() function:
Hold on, the result is different for the 29th than the 28th? This is what the title means with incorrect (or unexpected) results. Even though the data looks exactly the same in the table, the result are not.
It is not a bug in the DAX formula language. The problem resides with the duplicates in the table.
Read on to understand what this means and how you can change your code to prevent this issue.
Comments closedErik Svensen shows us what happens in DAX functions when February 29th rolls around:
Yesterday I visited a client and was asked – how do the time intelligence functions handle the fact that February has 29 days in 2020.
Well – in fact there was a few surprises depending on what you select from you date table.
Let’s look as some examples – I will use the following Internet Sales Amount from the years 2011-2013 from Adventure Work Database where we in February 2012 have 29 days.
Click through for those surprises. They aren’t bugs, as the functions have to do something and the possible set of valid somethings is greater than one.
Comments closedMarco Russo and Alberto Ferrari want you to watch your money grow:
Coincidentally, both debt instrument examples are what is known as “bullet” loans, where the entire principal amount ($100) is repaid in one lump sum at maturity (at the end of Year 5). In the first example the interest income payments are deferred until maturity, thereby allowing the interest to compound over the holding period. In the second example, the interest income payments are made at the end of each year, which means that the amount of debt accruing interest each year is always the same ($100).
Now let us consider a slightly more complex investment with compounding interest where the interest rate differs year-to-year. Because the interest rate varies, you can’t use the simple formula above (or its FV function equivalent in Excel). Rather, you must effectively stack each year on top of the preceding year and calculate year-by-year.
And that’s something you can do with DAX.
Comments closedShabnam Watson answers a couple of questions around aggregations and time series in Power BI:
I have received a couple of questions about Aggregations in Power BI and whether they can be used to cover time series calculations such as Year to Date, Quarter to Date, and Month To Date. The answer is yes. Since time series calculations break down into calculations over a series of days, an aggregation table defined at day level with the basic summarization methods (min, max, sum, count) and the right relationship with a Date dimension, can answer Year to Date, Quarter to Date, and Month To Date calculations.
Let’s take a quick look at one such calcualtion and how it can be covered with an aggration. I am going to use the same version of AdventureWorks sample database and Power BI model that I used in my previous blog post on aggregations, with a few changes.
Read on for a demonstration.
Comments closedMarco Russo shows us what’s new with DAX in 2019:
Microsoft released 13 new functions in 2019. The first 4 functions are related to the calculation group feature, which is now only available on Azure Analysis Services and Analysis Services 2019:
Read on for those 13 functions. Then, keep reading to see what Marco, Alberto, & crew have in mind for 2020.
Comments closedMarco Russo shows us how to build the equivalents of NULLIF and COALESCE in DAX:
The COALESCE function in ANSI SQL returns the current value of the first expression that does not evaluate to NULL. The syntax is:
COALESCE( <expression1>, <expression2>, <expression3>, … )We can rewrite this using IF in DAX this way:
Also read the big disclaimer at the top.
Comments closedReza Rad explains why you should use DAX variables if you’re repeating calculations:
We have to main parts in the expression above: A and B. Each of those is doing a calculation. Now, with the markings above, reading the expression is much simpler. The whole expression means this:
=IF(A>B, A, B)All the above expression is saying is that if A is bigger than B, then return A, otherwise B. Now it is much simpler to read it because we split the repetitive parts into sections. That is what exactly the DAX variable is for.
Readability is not the only benefit, however. Reza has more.
Comments closedTomaz Kastrun reviews the different DAX functions related to time in Power BI:
New functions are coming to DAX (Data Analysis Expression Language) regularly, and just recently, the function QUARTER was added. And this gave me thinking; why not do a standardized DAX, to be added as a “time dimension” to every Power BI, that I create.
Click through for that standardized time dimension.
Comments closedReza Rad wants to compare prior years to the current year, using year-to-date comparisons:
I have previously written about how to calculate the same period last year calculation and compare this year’s values with the last year’s values. However, sometimes, you don’t yet have the full year, especially for the current year. You might want to compare this year’s value with the last year’s value up until the same day but last year. This is what I call same period last year to date. Here in this blog article, I’ll explain how you can do that using DAX in Power BI. To learn more about Power BI, read Power BI from Rookie to Rock Star.
Click through to see how it’s done.
Comments closedCecilia Brusatori gives us a date dimension created with DAX:
We are going to follow the steps to manually create a dimension Date table with basic DAX. First thing, we need to create a new table.
Click through for the demo. There’s a lot more you can include in a date dimension, but this gives you the starting point.
Comments closed