Analyzing Sales for a Specific Time Period in Power BI: Mastering DAX Techniques

[Fowmy]

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 day occurring on the 2nd Sunday of every May, sets the stage for our analysis.

The challenge tasks us with two objectives:

1. 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.
2. 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.

We are going to create the following dashboard:

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:

 

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”:

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:

IMD Sales Amount =
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:

IMD Sales Trend =
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 from the attachment section below.

Visit my YouTube channel for Power BI videos.. 

Hope this article was helpful to you? Please leave your comments, suggestions or questions in the comments. 
Cheers!

ExcelFort - Analyzing Sales for a Specific Time Period in Power BI.zip

[JamesPearson83]

Hi, 

I have a similar scenario, but slightly different.

I am trying to write a DAX formula where by i can show a forecast that will end based a date from a project data table.

Forecast data with dates goes for up to 13 months (by week and rolling refresh)

Target data may finish before the 13 months (by week), but has an end date known

In one table I have forecast and sales data based on the key attributes; SKU and CustomerSalesRegion (Concatenated) and then target table data (same attributes reflected here) and relationships bridged back to our key master data from our ERP system. This all captured in weekly buckets.

The challenge i have is trying to end the forecast based on the dates from target table (multiple project names).

Effectively what i want to be able to do is select a project or SKU, then return only the forecast by week until it reaches the end date provided in the target table, without combining the tables in the dataset.

[JamesPearson83]

Dont worry i worked it out 🙂

it was as simple as using the "Filter" calc

Calculate (forecast LE, filter ( Table, Column Dates ))