Category: DAX

Reza 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 closed

Cecilia 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

Gilbert Quevauvilliers wants to see the specific values involved in a Power BI filter:

I was working with a customer trying to get them to better understand DAX and one if the things that is difficult to understand is how the filtering works in DAX.

The challenge I have found is that when using filters in a DAX measure I cannot visually see what is happening within the DAX Filter.

Below I will show you how I can see the values in the FILTER

Click through to see how.

Comments closed

Alexander Arvidsson shows how we can concatenate columns in Power BI using DAX:

Finally we can tackle the last hurdle – the column that shows both the current number of certifications and the requested goal. Had this been Excel it would have been dead easy – we just create a new cell that concatenates two other cells like this:

Then we copy the formula to all the rows. Easy. But this is not Excel. The “goal” part is simple – that’s just another column. The trick is to count all the other rows in the table with the same key. Let’s add the key column to the table so we see what we’re working with. “CompKey” is the concatenated key we created in the previous blog post. “Number of certs” is a count of the rows in the table, and because of row context it gets evaluated per key.

Read on for the solution.

Comments closed

Matt Allington takes us through the REMOVEFILTERS() function in DAX:

Recently Microsoft introduced a new function in DAX called REMOVEFILTERS(). This is a very useful and well named function and it does exactly what its name suggests. Its purpose is to act as a table filter parameter inside CALCULATE() as shown in the following example.

Total Sales All Products REMOVEFILTERS() = CALCULATE([Total Sales],REMOVEFILTERS(Products))

Read on to see how this compares to the prior/alternative solution and for more information on REMOVEFILTERS().

Comments closed

Rob Collie has some fun with DIVIDE():

A blank cell in a report is sometimes a source of confusion for those human beings consuming our work. “What does a blank cell mean,” they ask.  “It’s a division by zero,” we reply.  “Wut,” they then ask.  “Trust me,” we say, “you don’t want to see the alternative.”  “But I don’t trust you, and now I don’t trust this whole report,” is what they sometimes say next – whether under their breath or out loud.

But “N/A” is a lovely value to display.  It raises far fewer eyebrows.  “Oh, it says our Profit Margin % for electric blankets sold in Cancun is “N/A” – I get it, we’ve never sold that product there.”  No convo required.

Click through for the full story.

Comments closed

Alberto Ferrari lets us compare up to specific dates between years:

Unfortunately, the calculation is not perfect. At the year level, it compares the full previous year against an incomplete current year – in this example there are no sales after September 5th in the current year.

Besides, the problem appears not only at the year level, but also at the month level. Indeed, in September the Previous Year measure returns sales for the entire month of September in the previous year. The comparison is unfair, as there are only five days’ worth of sales in September of the current year.

Read on for a better technique.

Comments closed