In this model, the GPValue column represents margin, which can be replaced by a measure calculated from LineTotal minus cost. I'll start by deleting GPValue and then right-click on the SalesSummary table and select New quick measure. From the calculation drop-down and the mathematical operations section, I'll select subtraction. The base value will be the Sum of LineTotal, and the value to subtract will be Sum of cost. The quick measure wizard is a graphical interface for creating DAX statements, as we can see here. Next, I'll rename the measure to margin.  While the quick measure functionality is useful and convenient, it has its limitations when creating complex measures. For complex scenarios, we need to go it alone with DAX.

Measures are a calculated summary, meaning the values don't exist within the dataset but are calculated on the fly, and this is very important; within the context of a filter. Where at all possible, it is recommended to replace numeric columns with measures appropriate to the types of analysis your end users are likely to perform. The physics and mechanics of computer processors mean that calculating a number is often significantly faster than pulling rows of data from memory. This is particularly true when dealing with large datasets. There is, of course, a trade-off between the complexity of the measure calculation and pulling data from memory. If the calculation is very demanding and processor intensive, storing pre-calculated values as a calculated column may be a better option.

TaxRate, which holds the sales tax rate, is another candidate for measure substitution in that we don't need to know what it is but how it impacts other values such as LineTotal. It turns out that over the period that this dataset spans, the sales tax rate changed, so it isn't a case of just multiplying by some constant rate number. Let's go through the process of creating a generic and dynamic sales tax rate measure. The first thing I need to do is find out when the tax rate changed. I'll do that by using the calculate function, one of the most useful Power BI functions. Calculate takes two parameters; the first is the expression to be evaluated and returned as the result, the second is the filter, meaning the subset of data that the function will work on or use. 

The first parameter of calculate is an expression, so basically a measure or a column wrapped in a function, but not a column on its own.  Calculate will accept multiple filters. The filter parameters can be Boolean, that is evaluate to either true or false. Boolean filters can reference columns from a single table, cannot reference measures, or use nested calculate functions. Boolean filters can be chained together with and or or operators. A table expression uses a table object as a filter. This can be an actual table from the model but is typically a table object returned as a result of a complex filter condition using the Filter function. Filter modification functions such as RemoveFilters, KeepFilters, CrossFilter, and UseRelationship allow greater control than just adding filter conditions.

There are two tax rates involved here. The sales tax was 12.5% and was increased to 15%. I'll use calculate to return the maximum invoice date, so the last invoice where the tax rate was 12.5%. The expression is Max invoice date from SalesSummary. The filter is where the tax rate equals 0.125, which is 12 ½%. To ensure the dataset is correct, consistent, and doesn't contain any oddities, I'll use another calculate function to determine when the first invoice was created using the new 15% sales tax. This time, the minimum or earliest SalesSummary invoice date using the filter SalesSummary tax rate equals 0.15. Next, I'll drag the old tax rate and new tax rate measures onto a card. We can see the new tax rate came into effect on 1st October 2010, which I know to be the case.

Armed with this knowledge, we can create a measure that says if the invoice date is prior to 1st October 2010, multiply the LineTotal field by 1.125 to get the sales tax inclusive figure. Otherwise, multiply LineTotal by 1.15 to get the sales tax inclusive value after 1st October 2010. Sounds simple, doesn't it. If you're coming from a development or SQL background, this is where measures are less than intuitive. My first instinct is to create a sales tax rate measure using a simple if statement to calculate a tax-inclusive amount on the fly. When I try to do this Power BI says it can't find the invoice date field. This error is bogus because we know InvoiceDate exists. The error is in my thinking. I'm treating the measure as a function that will be applied to the whole dataset. As I said earlier, measures are calculated within the context of a filter, and as such, use some kind of aggregate function like sum, average, min, and max.

One way around this issue is to use the SUMX function. Before showing you that, I want to digress for a moment and explain the difference between SUM and SUMX. SUM, as in Excel, is an aggregation function. Within a context like in this example, dates in June, it will take one column, revenue, and sum all the values. SUMX and other X-functions are called iterative as the Power BI engine loops through all the values in the context range, adding them up. In this example, there is no difference in the end result. However, SUMX allows you to do additional processing while adding the row values together. The additional processing can be conditional on other values or factors and be arbitrarily complex. In the sales tax scenario, comparing SUM and SUMX side-by-side gives the best explanation of the differences. For any given filter context, whether that's a date range, product type, or geographic region, SUM will aggregate all the values. We multiply the aggregation total by the sales tax rate to get a total tax inclusive revenue figure. On the other hand, SUMX's iterative operation resembles that of a for-loop.

The SUMX function takes a table as its first argument, followed by an expression. In this case, the table is SalesSummary. The expression is an if statement where we multiply the LineTotal by 1.125, that is 12 ½% sales tax, if the invoice is before 1st October 2010, otherwise multiply by 1.15, 15% sales tax. Now, I can pop over to the report view, drop a matrix onto the report, use the invoice year calculated column for my rows and drag the new LineTotalAfterTax measure and LineTotal column onto the matrix. Next, I'll create another measure that will display the sales tax amount. Again, this will use the SUMX function to subtract the LineTotal field from the LineTotalAfterTax measure. Because LineTotalAfterTax uses SUMX, an iterative row by row function, it will always be operating in the same context as the row of the LineTotal value.

While this measure works and has allowed me to do away with the tax rate column, both the tax rates and the changeover date are literal values hardcoded into the DAX statement. If there were another change to sales tax, the measure would not easily cope with three sales tax rates. As sales taxes change over time, we would end up with a series of nested if statements.

Ideally, we would like a data-driven solution where the tax rates are stored in a table with applicable date ranges. Usually, you would have such a table in your data source, but I will create one here with the enter data function. Click the enter data button, which brings up the create table window, and I'll create a table with a start date, end date, and tax rate columns. I'll fill in the appropriate dates with the corresponding tax rates, call the table TaxRate, and click load. Tables created through the enter data function are the same as tables that you import from a data source as far as Power BI is concerned. I'll create a new measure called LookUpTaxRate, and I'll start by defining a variable called salesTaxFilter that will be used to retrieve the appropriate row from the TaxRates table. The filter function takes a table as its first parameter. The table parameter is the table context that the filter is working within. InvoiceDate from SalesSummary is not in the tax rates context, so it needs to be wrapped in an aggregate function, in this case, min. Remember, the minimum invoice date isn't the absolute minimum date but the minimum within the row's context or whatever slicer is currently active. I want the TaxRates row where the start date is less than or equal to the invoice date, and the end date is greater than the invoice date. Now that the filter has been defined, we need to use it to get the tax rate. I'll use the calculate function to return the maximum tax rate within the filter. There is only one tax rate per period, so that Max will work just fine. Finally, I'll return salesTaxRate. Having added LookUpTaxRate to the table, I'll increase the precision from 2 to 3, so we can see the 12 ½%. InvoiceYear doesn't have enough resolution or granularity for us to see the tax rate change. I'll quickly create a calculated column called InvoiceYearMonth to replace InvoiceYear. InvoiceYearMonth is the invoice date year multiplied by 100 with the invoice date month added to it.

Now I'll replace InvoiceYear with InvoiceYearMonth in the report table. Excellent, we can see the sales tax rate change happening in October 2010. Now we need to replace the if then else logic within the LineTotalAfterTax measure. Sticking with the SUMX function, I'll remove the if statement and just multiply the LineTotal by one plus the LookupTaxRate. It looks like we've got a little bit of rounding happening here as three of the months' after-tax totals change, and the grand total does decrease by two dollars. Let's see how generic the solution is by adding another sales tax rate to the tax rates table. I'll need to go into Power Query via the transform menu to edit an existing table's data. In Power Query, click on the table you want to edit, and then over on the right, under applied steps, click on the gear icon next to source. This opens up the original create table window. Oh, I can see the end date for my first period is wrong. I'll slot in a lower tax rate of 10% that expires on the 31st of August 2009. Then I'll move the 12 ½% tax rate to be effective from September 2009 up to the end of September 2010. Click OK, and then don't forget to click close and apply. After a little bit of thinking, Power BI refreshes the report data showing us the new tax rates.