Fill Blank Rows With Previous Non-Blank Values – DAX

Fill Blank Rows With Previous Non-Blank Values – DAX

I want to see a value for everyday, whether a new value exists or not – lets call it tracking the members of a golf club. If there are no changes to the member numbers, I would have no movement figure on that day. If someone joined or left, the figure would be updated. Just because there are no changes, it doesn’t mean that there are no members on that date and therefore this day is just the membership figure from the day before, or previous non blank date. So we need to fill blank rows with the previous non-blank value to keep tracking of membership figures accurately.

It might look like below…where ‘Goal’ is how we want our report measure to look:

We can achieve this by the following simple logic:

• Get the previous non-blank date
  • Check today, inclusive
• Filter the membership figure to the previous non-blank date

The starting point is checking that the calculation identifies the correct date – as below:

It is important to make sure that the operator is <= to capture that days value as we want that day to be consistent – if it exists, keep it. Also, we apply the ALL function to the Date table to make sure that we can see the Date rows with no items (blanks). Similarly, we want to get the previous value, whether it is on the visual or not. You can configure this to suit your requirements, maybe you want the last non zero, just change “” to 0. You can see my explanation as to why I use “” instead of BLANK() here.

Once we have the correct date being identified for each row, we want to calculate the membership figure. Firstly, create a suitable measure for your column – we can use
SUM(‘Table’[Membership])
to get the total memberships on that day for example.

Now we can achieve our goal:

So all together, your measure to get the previous non-blank value will look like this:

Be sure to check out my other blog on how BLANK() behaves in DAX.

Tags: Business, Business Intelligence, DAX, Power BI, Reporting Services