Before() - calculation with empty/NULL fields

[TheOtherMartin]

I have a question with the behaviour of the before() function.

I have a table with customers, articles and then I am showing the total sold. In order to show the change in sales I use the before() function to calculate the evolution over the years:

(Sum(SalesInvoiceLine.LineAmount)-
    before(Sum(SalesInvoiceLine.LineAmount)))
    /
    before(Sum(SalesInvoiceLine.LineAmount))
)

My Problem is with an article which in 2017 has not been sold, but in 2016 it has been - the variation is shown as -0,6 which is not true - well, it is true if I wanted to know the change to the last time I sold this item. But I'd like to show here the change to 2017. How can I acomplish this? Sample data attached.

[sunny_talwar · Accepted solution]

I suggest you to use The As Of Table instead of trying to fix this on the front end. Not to say that it is not possible, but it will be a messy

[TheOtherMartin · Accepted solution]

Thank you for your fast response. The solution you suggested works, but brings me to the result that the first column of the Variance is Empty, which is the correct behaviour of the before() function.

I even found another solution, but as you said, a messy one and a less dynamic one. Instead of using the before() function I removed the Year Dimension and recreated all columns by using a set analysis.

This works as expected, also showing a variance in the first column but is less dynamic as every column has to be "hardcoded" using set analysis.

[sunny_talwar]

But did you get a chance to look at The As Of Table link that I shared?

[TheOtherMartin]

Yes, but this leads me to the result where the first variation column is empty (which is the correct behaviour for the before function). Another, not mentioned before, feature of my tabe was that this column has to be filled with values.

In this screenshot my table above vs. the solution with the AsOfYear as Dimension (the lines are article codes).

[sunny_talwar]

If implemented correctly, I don't think you should be seeing this problem. Difficult to say without seeing what you had and what you did.