Welcome to another exciting DAX challenge workout! In this blog post, we will delve into the world of Data Analysis Expressions (DAX) in Power BI and explore how to calculate sales for a specific time period. This challenge, titled “DAX Workout 017 – Calculating Sales for a Specific Time Period,” was published by EnterpriseDNA on their website. Join me as I walk you through my approach and solution to this intriguing challenge.
Understanding the Challenge: International Mother’s Day ( Referred as IMD in this article), a widely celebrated holiday occurring on the 2nd Sunday of every May, sets the stage for our analysis.
The challenge tasks us with two objectives:
- Create a DAX measure to determine the total sales for each year, specifically on Mother’s Day as shown on the left column chart below.
- Develop a DAX measure to showcase the sales for each day in the week leading up to Mother’s Day. The Area chart shows the expected output below. This allows you to compare between years.
Dataset and Preparation: To tackle this challenge, I leveraged a source dataset containing daily product sales from 2021 to 2023. Additionally, I created a Dates table to facilitate the analysis and make use of DAX time intelligence functions. Here’s a glimpse of the dataset:
Approach and Solution: To achieve our objectives, I followed a systematic approach. Let’s take a closer look at the steps I took to complete the challenge successfully.
Step 1: Identifying the IMD Dates. I added a column to the Dates table to determine the date on which Mother’s Day falls in each year. Additionally, I marked the last seven days leading up to that date. This information is crucial for our trend analysis in answering the second question. Here’s the DAX formula I used in the calculated column called IMD:
VAR __Year = 'Dates'[Year]
VAR __DatesRange =
FILTER ( Dates, Dates[YEAR] = __Year && Dates[Month] = 5 && Dates[WeekDay] = 1 )
VAR __MoDate =
INDEX ( 2, __DatesRange, ORDERBY ( Dates[Date], ASC ) )
VAR __DayDiff =
MAXX ( __MoDate, Dates[Date] ) - Dates[Date]
RETURN
SWITCH ( TRUE (), __DayDiff < 8 && __DayDiff >= 0, __DayDiff )
Step 2: To achieve synchronization of the X-axis in the line chart and enable small multiples split by different years, I introduced a column called “IMD Axis” into the dates table. This column assigns meaningful values to the IMD column, facilitating effective trend visualization. In this formula, I designated 0 to represent the IMD, while the previous seven days are denoted as -1 Day, -2 Days, and so on. Here’s the DAX formula I employed in the calculated column named “IMD Axis”:
VAR __Day = 'Dates'[IMD]
RETURN
SWITCH (
TRUE (),
__Day == 0, REPT ( UNICHAR ( 8203 ), 10 ) & "IMD",
__Day = 1,
"-" & __Day & " Day",
__Day IN { 2, 3, 4, 5, 6, 7 },
"-" & __Day & " Days"
)
To ensure that the IMD remains at the end when sorted, I utilized the REPT(UNICHAR(8203), 10) function to add 10 leading empty spaces. By including these spaces, I preserved the desired order of the IMD and prevented it from appearing after the -7 Days. This technique helps maintain a clear and intuitive visualization of the time period in the line chart.
Step 3: To accurately calculate sales specifically on Mother’s Day (IMD), I implemented a measure in DAX. It was crucial to ensure strict evaluation and exclude values with blanks, as DAX treats blanks and zeros as equivalent when using the “=” operator, this is called the strict evaluation.
To overcome this, I leveraged the “==” operator in the formula. By doing so, I precisely calculated sales only for the desired IMD dates while excluding any blank values. Here’s the DAX formula I employed for this measure:
CALCULATE ( [Sales Amount], Dates[IMD] == 0 )
Step 4: To address the first question, I created a Column Chart that illustrates the IMD sales for each year. The X-axis represents the years, while the Y-axis displays the IMD Sales Amount measure.
Step 5: Analyzing Trend and Past Week Sales Next, I developed a measure to showcase the trend of IMD sales and the sales for the past week. This measure enables us to visualize the changing sales patterns leading up to Mother’s Day. Here’s the DAX formula I used:
CALCULATE ( [Sales Amount], NOT ( Dates[IMD] == BLANK () ) )
Step 6: Visualizing the Trend on a Area Chart is the 2nd Objective. To present the trend effectively on a area chart and split it by different years using the Small Multiple option, I employed the calculated column “IMD Axis” on the X-axis which we saw earlier. This ensures synchronization at the same level vertically. Here’s an image demonstrating the resulting area chart:
Congratulations! You’ve now mastered the art of analyzing sales for a specific time period using DAX in Power BI. In this blog post, we explored a challenging workout provided by EnterpriseDNA and shared my approach and solution step-by-step. By applying DAX formulas and leveraging Power BI’s capabilities, we were able to extract valuable insights from the dataset and visualize the sales trends effectively.
Remember, DAX challenges like these are excellent opportunities to enhance your skills and discover new techniques. Keep practicing, and soon you’ll become better at it.
Download the PBIX file used in this exercise: HERE
Hope this article was helpful to you? Please leave your comments, suggestions or questions in the comments.
Cheers!
Fowmy Abdulmuttalib is a Microsoft-certified business intelligence professional with extensive experience in finance, accounting and data analytics. Currently, he is employed by McDonald’s Saudi Arabia as the Head of ERP and Business Intelligence. He is interested in everything data-related, he likes to share his passion and knowledge with the data community.