Getting Previous Row Value in Power Query

Someone asked how he could get a calculated column with the value for a certain column in the previous row. In Excel, this is a simple task. However in M, things become a bit more tricky.

Suppose you are taking measurements, and for some reason you want the previous measurement to be stored with your data. There are optimization concerns that may make this a useful thing to do, as Power Query only performs its actions on refresh, and not once the data loaded. Alternatively, DAX does it’s work every time you select a slider, click a visualization, or basically change anything on the report.

This is one of the times when a good SQL background helps your M development.

Counting Measure Results

Someone had written a measure that gave a certain range of outputs, such as “A”, “B”, or “C”. They had a process that gave a grade to each order depending on how well it performed in certain ways — how fast it was shipped, how many times the customer called in, how much profit was made, etc. They then wanted to know how many As, Bs, and Cs were given. In short, they wanted to be able to count the measure results.

When you want to measure other measures, you need the iterative functions (a.k.a the “X” functions).

In this case they wanted COUNTX().

Estimating When a Product was Created by When it was Sold

A client wanted to know about sales of items by when they were available. They wanted to be able to see the sales of products introduced in 2016 vs the sales of products introduced in 2017. Presumably they wanted to make sure that the products they were taking the time to find, load, merchandise, and sell were better or worse this year over last.

Unfortunately, the records of when the products were introduced were spotty at best. There was no created_at column on their product table, and they had only recently started recording that in a product history table.  That is not uncommon, but it is unfortunate.

Start of Current Month in Selected Year

Someone asked how he could find the start of the current month in a different year. He had a report that contained a parameter that selected a year, say 2007. He wanted to find the beginning of the current month, say May, in that year. So what he wanted was the string “May 1, 2007” when the current date was May 26, 2018. And if the current date were October 5, 2023, he would want the string to read “October 1, 2007”.

Whenever you talk about dates in Power BI, you have to know how dates are stored. In this case, the date table was created with the DAX function

Dates = CALENDAR(DATE(2001,1,1), TODAY())

That gives you a table named Dates with a column named Date, and each row is a day.

Normalize DAX Measures in Power BI

I know how to do it. I will tell you more soon.