- Subscribe to RSS Feed
- Mark as New
- Mark as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content
Analyzing Sales for a Specific Time Period in Power BI: Mastering DAX Techniques
- Subscribe to RSS Feed
- Mark as New
- Mark as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content
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!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- While Loop in M Language - In Short
- 🌟 Judges’ Favorites: Honoring More Standout Entri...
- 🎉 And the Winners Are… Celebrating the Best of th...
- 🎉Power BI Turns 10! Grab your digital swag!
- Level Up Your Reports: Mastering Power BI Performa...
- Integrate Python Scripts in Power BI
- ISINSCOPE vs HASONEVALUE in Power BI: The Real Dif...
- Great-Looking Dashboard, Bad Decisions: How Poor D...
- Button Slicer / How to Make Images as Slicers in P...
- Power BI Enhancements You Need to Know – Part 7: V...
-
LucasKoch2900
on: 🌟 Judges’ Favorites: Honoring More Standout Entri...
-
LucasKoch2900
on: 🎉 And the Winners Are… Celebrating the Best of th...
-
Leanore
on: 🎉Power BI Turns 10! Grab your digital swag!
- sgunasekhar1 on: Level Up Your Reports: Mastering Power BI Performa...
-
bhanu_gautam on: ISINSCOPE vs HASONEVALUE in Power BI: The Real Dif...
-
v-agajavelly on: Button Slicer / How to Make Images as Slicers in P...
- lokesh0909 on: Unlocking the Power of Calculation Group - Beyond ...
-
Abhilash_P
on: Seamless Power BI Report Management with SharePoin...
-
Magudeswaran_MR
on: Field Parameters in Power BI
- Vikranth426 on: How to Organize Measures Effectively in Power BI
-
How to
671 -
Tips & Tricks
648 -
Events
146 -
Support insights
121 -
Opinion
82 -
DAX
66 -
Power BI
65 -
Power Query
62 -
Power BI Dev Camp
45 -
Power BI Desktop
40 -
Roundup
37 -
Dataflow
23 -
Featured User Group Leader
21 -
Power BI Embedded
20 -
Time Intelligence
19 -
Tips&Tricks
18 -
PowerBI REST API
12 -
Data Protection
11 -
Power BI Service
8 -
Power Query Tips & Tricks
8 -
finance
8 -
Direct Query
7 -
Power BI REST API
6 -
Auto ML
6 -
financial reporting
6 -
Data Analysis
6 -
Power Automate
6 -
Data Visualization
6 -
Python
6 -
powerbi
5 -
service
5 -
Power BI PowerShell
5 -
Machine Learning
5 -
Income Statement
5 -
Dax studio
5 -
Paginated Reports
4 -
External tool
4 -
Power BI Goals
4 -
Desktop
4 -
PowerShell
4 -
Bookmarks
4 -
Line chart
4 -
Group By
4 -
community
4 -
RLS
4 -
M language
4 -
R script
3 -
Aggregation
3 -
calendar
3 -
Gateways
3 -
Webinar
3 -
R
3 -
M Query
3 -
CALCULATE
3 -
R visual
3 -
Reports
3 -
PowerApps
3 -
Tips and Tricks
3 -
Data Science
3 -
Azure
3 -
Data model
3 -
Conditional Formatting
3 -
Visualisation
3 -
Life Sciences
3 -
Administration
3 -
M code
3 -
Visuals
3 -
SQL Server 2017 Express Edition
3 -
Row and column conversion
2 -
Python script
2 -
Nulls
2 -
DVW Analytics
2 -
parameter
2 -
Industrial App Store
2 -
Week
2 -
Date duration
2 -
Formatting
2 -
Weekday Calendar
2 -
Support insights.
2 -
construct list
2 -
slicers
2 -
SAP
2 -
Power Platform
2 -
Workday
2 -
external tools
2 -
index
2 -
RANKX
2 -
PBI Desktop
2 -
Date Dimension
2 -
Integer
2 -
Date
2 -
Visualization
2 -
Power BI Challenge
2 -
Query Parameter
2 -
SharePoint
2 -
Power BI Installation and Updates
2 -
How Things Work
2 -
Tabular Editor
2 -
rank
2 -
ladataweb
2 -
Troubleshooting
2 -
Date DIFF
2 -
Transform data
2 -
Healthcare
2 -
Incremental Refresh
2 -
Number Ranges
2 -
Query Plans
2 -
Power BI & Power Apps
2 -
Random numbers
2 -
Day of the Week
2 -
Custom Visual
2 -
VLOOKUP
2 -
pivot
2 -
calculated column
2 -
M
2 -
hierarchies
2 -
Power BI Anniversary
2 -
Language M
2 -
inexact
2 -
Date Comparison
2 -
Power BI Premium Per user
2 -
Forecasting
2 -
REST API
2 -
Editor
2 -
Split
2 -
measure
2 -
Microsoft-flow
2 -
Paginated Report Builder
2 -
Working with Non Standatd Periods
2 -
powerbi.tips
2 -
Custom function
2 -
Reverse
2 -
PUG
2 -
Custom Measures
2 -
Filtering
2 -
Q&A
1 -
Table
1 -
Natural Query Language
1 -
Infographic
1 -
automation
1 -
Prediction
1 -
newworkspacepowerbi
1 -
Performance KPIs
1 -
HR Analytics
1 -
keepfilters
1 -
Connect Data
1 -
Financial Year
1 -
Schneider
1 -
dynamically delete records
1 -
Copy Measures
1 -
Friday
1 -
update
1 -
Event
1 -
Custom Visuals
1 -
Free vs Pro
1 -
Format
1 -
Active Employee
1 -
Custom Date Range on Date Slicer
1 -
refresh error
1 -
PAS
1 -
certain duration
1 -
DA-100
1 -
bulk renaming of columns
1 -
Single Date Picker
1 -
Monday
1 -
PCS
1 -
Saturday
1 -
Training
1 -
Slicer
1 -
Visual
1 -
forecast
1 -
Regression
1 -
CICD
1 -
Current Employees
1 -
date hierarchy
1 -
relationship
1 -
SIEMENS
1 -
Multiple Currency
1 -
Power BI Premium
1 -
On-premises data gateway
1 -
Binary
1 -
Power BI Connector for SAP
1 -
Sunday
1 -
Announcement
1 -
Features
1 -
domain
1 -
pbiviz
1 -
sport statistics
1 -
Intelligent Plant
1 -
Circular dependency
1 -
GE
1 -
Exchange rate
1 -
Dendrogram
1 -
range of values
1 -
activity log
1 -
Decimal
1 -
Charticulator Challenge
1 -
Field parameters
1 -
Workspace
1 -
deployment
1 -
ssrs traffic light indicators
1 -
SQL
1 -
trick
1 -
Scripts
1 -
Color Map
1 -
Industrial
1 -
Weekday
1 -
Working Date
1 -
Space Issue
1 -
Emerson
1 -
Date Table
1 -
Cluster Analysis
1 -
Stacked Area Chart
1 -
union tables
1 -
Number
1 -
Start of Week
1 -
Tips& Tricks
1 -
Theme Colours
1 -
Text
1 -
Flow
1 -
Publish to Web
1 -
Extract
1 -
Topper Color On Map
1 -
Historians
1 -
context transition
1 -
Custom textbox
1 -
OPC
1 -
Zabbix
1 -
Label: DAX
1 -
Business Analysis
1 -
Supporting Insight
1 -
rank value
1 -
Synapse
1 -
End of Week
1 -
Tips&Trick
1 -
Showcase
1 -
custom connector
1 -
Waterfall Chart
1 -
Power BI On-Premise Data Gateway
1 -
patch
1 -
Top Category Color
1 -
A&E data
1 -
Previous Order
1 -
Substring
1 -
Wonderware
1 -
Power M
1 -
Format DAX
1 -
Custom functions
1 -
accumulative
1 -
DAX&Power Query
1 -
Premium Per User
1 -
GENERATESERIES
1 -
Excel
1 -
Report Server
1 -
Audit Logs
1 -
analytics pane
1 -
step by step
1 -
Top Brand Color on Map
1 -
Tutorial
1 -
Previous Date
1 -
XMLA End point
1 -
color reference
1 -
Date Time
1 -
Marker
1 -
Lineage
1 -
CSV file
1 -
conditional accumulative
1 -
Matrix Subtotal
1 -
Check
1 -
null value
1 -
Cumulative Totals
1 -
Report Theme
1 -
Bookmarking
1 -
oracle
1 -
mahak
1 -
pandas
1 -
Networkdays
1 -
Button
1 -
Dataset list
1 -
Keyboard Shortcuts
1 -
Fill Function
1 -
LOOKUPVALUE()
1 -
Tips &Tricks
1 -
Plotly package
1 -
Sameperiodlastyear
1 -
Office Theme
1 -
matrix
1 -
bar chart
1 -
Measures
1 -
powerbi argentina
1 -
Canvas Apps
1 -
total
1 -
Filter context
1 -
Difference between two dates
1 -
get data
1 -
OSI
1 -
Query format convert
1 -
ETL
1 -
Json files
1 -
Merge Rows
1 -
CONCATENATEX()
1 -
take over Datasets;
1 -
Networkdays.Intl
1 -
refresh M language Python script Support Insights
1 -
Tutorial Requests
1 -
Governance
1 -
Fun
1 -
Power BI gateway
1 -
gateway
1 -
Elementary
1 -
Custom filters
1 -
Vertipaq Analyzer
1 -
powerbi cordoba
1 -
Model Driven Apps
1 -
REMOVEFILTERS
1 -
XMLA endpoint
1 -
translations
1 -
OSI pi
1 -
Parquet
1 -
Change rows to columns
1 -
remove spaces
1 -
Get row and column totals
1 -
Retail
1 -
Power BI Report Server
1 -
School
1 -
Cost-Benefit Analysis
1 -
DIisconnected Tables
1 -
Sandbox
1 -
Honeywell
1 -
Combine queries
1 -
X axis at different granularity
1 -
ADLS
1 -
Primary Key
1 -
Microsoft 365 usage analytics data
1 -
Randomly filter
1 -
Week of the Day
1 -
Azure AAD
1 -
query
1 -
Dynamic Visuals
1 -
KPI
1 -
Intro
1 -
Icons
1 -
ISV
1 -
Ties
1 -
unpivot
1 -
Practice Model
1 -
Continuous streak
1 -
ProcessVue
1 -
Create function
1 -
Table.Schema
1 -
Acknowledging
1 -
Postman
1 -
Text.ContainsAny
1 -
Power BI Show
1 -
Get latest sign-in data for each user
1 -
API
1 -
Kingsley
1 -
Merge
1 -
variable
1 -
Issues
1 -
function
1 -
stacked column chart
1 -
ho
1 -
ABB
1 -
KNN algorithm
1 -
List.Zip
1 -
optimization
1 -
Artificial Intelligence
1 -
Map Visual
1 -
Text.ContainsAll
1 -
Tuesday
1 -
Power Pivot
1 -
help
1 -
group
1 -
Scorecard
1 -
Json
1 -
Tops
1 -
financial reporting hierarchies RLS
1 -
Featured Data Stories
1 -
MQTT
1 -
Custom Periods
1 -
Partial group
1 -
Reduce Size
1 -
FBL3N
1 -
Wednesday
1 -
Quick Tips
1 -
data
1 -
PBIRS
1 -
Usage Metrics in Power BI
1 -
Multivalued column
1 -
Pipeline
1 -
Path
1 -
Yokogawa
1 -
Dynamic calculation
1 -
Data Wrangling
1 -
native folded query
1 -
transform table
1 -
UX
1 -
Cell content
1 -
General Ledger
1 -
Thursday
1
- 07-20-2025 - 07-26-2025
- 07-13-2025 - 07-19-2025
- 07-06-2025 - 07-12-2025
- 06-29-2025 - 07-05-2025
- 06-22-2025 - 06-28-2025
- 06-15-2025 - 06-21-2025
- 06-08-2025 - 06-14-2025
- 06-01-2025 - 06-07-2025
- 05-25-2025 - 05-31-2025
- 05-18-2025 - 05-24-2025
- 05-11-2025 - 05-17-2025
- 05-04-2025 - 05-10-2025
- 04-27-2025 - 05-03-2025
- View Complete Archives