As a distributor, we offer a ‘spend and profitability’ report in Power BI as an added value for our customers. This report serves two purposes:
1. To provide the customer with a daily tracker of purchases made during the month.
2. To calculate different profitability measures and ratios after month end.
The reporting challenge is writing profit measures in DAX that accounts for the time lag between reported purchases, and the later entry (sometimes two weeks after month end) of total month customer sales. We need DAX measures to essentially wait for both entries (total purchase and total sales) to be made first before calculating profit and other KPI ratios.
In this post we will walk through using the DAX function TREATAS as a solution, then provide the sample file at the end of the post. TREATAS is not a function I use often, but one that has proven to bring flexibility and improved accuracy to my reports.
The image below illustrates what happens to profitability when we don’t have timely sales amounts entered into the ‘Sales Table‘. In March 2020, without entering the ‘Sales Table‘ entry, our basic profit measures will understate ‘Profit‘ dollars and dilute ‘Margin %‘ at the total line.
Here is the resulting report in Power BI:
If we could account for this time lag in our profit measures, we would restrict our profit measures to completed months as entered in both tables. More importantly, our profit results would then be correct at the Grand Total line of the report.
TREATAS is a DAX function that allows us to transfer a virtual relationship between tables. It is similar, yet performs better than using INTERSECT between tables in DAX. To learn more about the TREATAS function, click here.
We’ll use TREATAS to restrict our list of ‘Mo Year‘ values to match those used in our ‘Sales Table‘; then we apply that restricted list of ‘Mo Year‘ values to our ‘Date Table‘.
Let’s use the TREATAS function in the ‘Profit NEW‘ measure below, then create a ‘Margin % NEW‘ measure to see how the results change our report:
The SalesMonths variable restricts our months within the ‘Sales Table‘ by filtering only the populated Sales rows (non blank rows), it provides the list we need to use in our TREATAS function.
TREATAS takes our reduced list of months and transfers the list to our ‘Date Table‘. One way to read the above TREATAS statement, is that we TREAT our variable of Mo Year values AS the filter applied to the ‘Date Table'[Mo Year] column.
When this list of dates propagates through to ‘Sales Table‘ and ‘Purchase Table‘, we ensure that our new profit measure only evaluates for January and February over both tables.
By adding this new measure to our report below, we now get the expected result. Even though we have March purchases, our Profit NEW filters only up through February. Since March is not visible in the filter context of this measure, there is no evaluation of profit for March.
This is a simple example of using a single column to create and transfer a virtual relationship to different tables. Other more advanced options of TREATAS include working with multiple columns. To learn more click here.
To download the sample PBIX file used in this post, click below.
As always, thanks for reading! – Mark
Leave a Reply