Data Analysis Expressions (DAX) is a powerful formula language that can help you solve challenging problems and leverage the full potential of Power BI or Analysis Services Tabular when implementing business intelligence, data modeling, or analytics solutions. So, in this blog post, I will demonstrate a useful DAX trick we used to address a challenge we faced during a business intelligence implementation for one of our clients, who is a leader in the regional FMCG industry.
Problem Scenario
Assume you are a sales representative and you want to calculate your sales achievement percentage in the middle of the month. But, as shown in the figure below, you do not have data from the ‘Current Date’ up to the ‘End of Month’ (MTD).
Table 1: Data Sheet
If we calculate the sales achievement for the month without the sales data between current date and end of the month using:
Sales Achievement = (Sales/Target) x 100
The sales achievement will be:
(503/1476) x 100 = 38.08%
Which is incorrect as without the missing data, you cannot calculate the sales achievement accurately.
How can we implement this scenario in a tabular model?
The simplest way to calculate MTD values using DAX is to use the DATEMTD function.
For example:
MTD Target :=CALCULATE (SUM(Target), DATESMTD(‘Calendar’[Date])
MTD Sales :=CALCULATE (SUM(Sales), DATESMTD(‘Calendar’[Date])
These formulas return the following MTD figures:
The formulas still calculate target up to the end of the month. We need to fix the DAX code to filter data until the current date. To do this, we should add the following filter to the DAX code.
FILTER ( 'Calendar', 'Calendar'[Date] < TODAY () )
With the filter, the DAX code looks as follows:
Using this method, we can calculate the achievement percentage for the current month using the DATEMTD function.Author:Muditha Pelpola Consultant – Business Intelligence
Muditha is a Consultant in the Business Intelligence division of Fortude. He has more than 6 years’ experience in the software industry including development of data warehouses, reporting and analytics solutions in the Apparel, FMCG, and Healthcare domains.
