- Subscribe to RSS Feed
- Mark as New
- Mark as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content
Comparing Selected Month Sales against other months Sales using INDEX DAX function.
- Subscribe to RSS Feed
- Mark as New
- Mark as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content
Recently, I came across a requirement where the user selects the month number in the slicer, and based on that, the sales values in the table visual will be compared with sales values of other months using conditional formatting.
Here, I’ll provide a step-by-step implementation of the same using the INDEX DAX function in Power BI,
Before we jump into the implementation, let’s take a closer look at the INDEX function.
The INDEX function retrieves a row from a table based on a specific position indicated by the position parameter. It works within a defined partition and considers the specified order for sorting.
Syntax:
INDEX(<position>[, <relation>][, <orderBy>][, <blanks>][, <partitionBy>][, <matchBy>])
position – The absolute position (1-based) from which to obtain the data:
Position is positive: 1 is the first row, 2 is the second row, etc.
Position is negative: -1 is the last row, -2 is the second last row, etc.
relation – (Optional) A table expression from which the output is returned.
orderBy – (Optional) An ORDERBY() clause containing the expressions that define how each partition is sorted.
blanks – (Optional) An enumeration that defines how to handle blank values when sorting. Currently, the only supported value is DEFAULT.
partitionBy – Optional) A PARTITIONBY() clause containing the columns that define how <relation> is partitioned.
matchBy – (Optional) A MATCHBY() clause containing the columns that define how to match data and identify the current row.
I hope the above explanation provided you with some idea about the INDEX function. If you want to delve deeper into this function, I would recommend referring to the official Microsoft documentation.
Let’s begin the implementation of comparing selected month sales vs. other months using the INDEX function in Power BI.
Requirement:
When the user selects the month number in the slicer, the sales values of the current month will be compared with the selected month in the table visual using up and down arrows to indicate the increase and decrease in sales.
Implemetation:
Data Modelling
Create Necessary Tables and Columns
Dim Date – We need to create a proper date table with all the required columns based on our specific requirements. This date table will be connected to our fact table. Here, I have defined the date table using the CALENDAR function, but we can also create it using the Power Query Editor or obtain a pre-defined date table from any source that meets our requirements. I intend to initiate the date table from January 1st, 2013.
Dim Date = CALENDAR(
DATE(2013,01,01),TODAY())
Month Number Parameter – Under the Modeling tab, select “New Parameter” (Numeric Range) option and fill in the required information as shown in the image below. Then, click on “Create” to create the parameter. We will use this parameter value in the slicer to allow the user to select the month.
Tables used:
Financials – This table contains sales and profit information.
Dim Date – This table holds the date and other columns required for the implementation.
Month Number (Parameter) – This parameter table contains the month numbers from 1 to 12 to be used in the slicer.
We have created one-to-many relation between Dim Date and financials table with ‘Dim Date'[Date] and ‘financials'[ Date] as a relationship key between the tables.
DAX Measures:
Sales Measure
Sales Sales Measure =
// Define a variable to store the selected month number from the 'Month Number' table.
VAR _selectedMonth = SELECTEDVALUE('Month Number'[Month Number])
// Define a variable to store the selected month name from the 'Dim Date' table.
VAR _selectedMonthName = SELECTEDVALUE('Dim Date'[MonthName])
// Calculate the sales for the current month using the 'financials' table and 'INDEX' function.
VAR _currentMonthSales =
CALCULATE(
SUM(financials[ Sales]),
INDEX(
_selectedMonth,
ALL('Dim Date'[Month Number], 'Dim Date'[Year]),
ORDERBY('Dim Date'[Month Number], ASC),
DEFAULT,
PARTITIONBY('Dim Date'[Year])
),
ALL('Dim Date')
)
// Calculate the total sales for the selected month using the 'financials' table.
VAR _monthSales = SUM(financials[ Sales])
// Check if the current month is the same as the selected month.
// If it is, display the message with the selected month name and its sales value.
// Otherwise, display the total sales for the selected month.
VAR _result =
IF(
SELECTEDVALUE('Dim Date'[Month Number]) = _selectedMonth,
"Selected Current Month is " & _selectedMonthName & " (" & _currentMonthSales & ")",
_monthSales
)
// Return the result based on the conditions above.
RETURN _result
Selected Month Conditional Formatting
This measure is used for conditional (Increase/Decrease) formatting in Power BI table visual,
Selected Month Conditional Formatting =
// Define a variable to store the selected month number from the 'Month Number' table.
VAR _selectedMonth = SELECTEDVALUE('Month Number'[Month Number])
// Calculate the sales for the selected month using the 'financials' table and 'INDEX' function.
VAR _monthSales =
CALCULATE(
SUM(financials[Sales]),
INDEX(
_selectedMonth,
ALL('Dim Date'[Month Number], 'Dim Date'[Year]),
ORDERBY('Dim Date'[Month Number], ASC),
DEFAULT,
PARTITIONBY('Dim Date'[Year])
),
ALL('Dim Date')
)
// Define a variable to store the result of the conditional formatting.
// If the current month is the same as the selected month, display "Selected Current Month."
// Otherwise, check if the sales for the selected month are greater than the calculated '_monthSales'.
// If yes, return 1 (for formatting an increase indicator), else return 0 (for formatting a decrease indicator).
VAR _result =
IF(
SELECTEDVALUE('Dim Date'[Month Number]) = _selectedMonth,
"Selected Current Month",
IF(
SUM(financials[Sales]) > _monthSales,
1,
0
)
)
// Return the result for conditional formatting.
RETURN _result
Selected Month Name (Subtitle)
This measure is used in the subtitle to provide meaningful information to the user based on the slicer selection,
Selected Month Name (Subtitle) =
// Define a variable to store the selected month number from the 'Month Number' table.
VAR _selectedMonthNumber = SELECTEDVALUE('Month Number'[Month Number])
// Calculate the month name for the selected month using the 'Dim Date' table and 'CALCULATE' function.
VAR _monthName =
CALCULATE(
SELECTEDVALUE('Dim Date'[MonthName]),
'Dim Date'[Month Number] = _selectedMonthNumber
)
// Define a variable to store the formatted subtitle with the selected month name.
// The subtitle will indicate a comparison of the selected month with other months.
VAR _selectedMonthName = "Comparison of " & _monthName & " with other months"
// Return the formatted subtitle.
RETURN _selectedMonthName
Reporting
We will be using the slicer and table visual in Power BI. Please follow the steps below for the implementation:
Step 1:
First, we need to create a slicer to select the month number. To do that, we will use the “Month Number” column from the parameter table in the slicer. I have randomly selected month number 5.
Note: I have customized the title background and values background based on my preference. Feel free to format the visual according to your own preferences as well.
Step 2:
Drag and drop the “MonthName” column from the “Dim Date” table onto the table visual canvas. Next, drag and drop the “Sales” measure into the table visual. Your visual would look like this,
Step 3:
To apply conditional formatting, you need to use the“Selected Month Conditional Formatting” measure in the conditional formatting option.
To access the conditional formatting option, right-click on the “Sales” measure in the “Values” field pane of the “Sales” table, and then choose “Conditional formatting” followed by “Icons.”
Step 4:
In the conditional formatting pane, select the following highlighted values from the dropdowns: for the value 1, an up-arrow with green color to represent higher sales, and for the value 0, a down-arrow with red color to indicate lesser sales. Please ensure that the plotting is done carefully to achieve the desired visual representation.
Step 5: Title and Subtitle
To create the title, you can directly input the text values in the format pane of the text title placeholder. However, for the subtitle, you need to select conditional formatting in the subtitle option to make the subtitle text work dynamically using the “Selected Month Name (Subtitle)” measure.
Title:
Subtitle:
Select the “fx” (function) icon here, and choose the necessary fields in the conditional formatting pane as mentioned, similar to the examples shown in the images below.
Result:
Note: I have customized the title background and values background based on my preference. Feel free to format the visual according to your own preferences as well.
Please refer the attached file and let me know your comments.
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
672 -
Tips & Tricks
649 -
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 -
financial reporting
6 -
Data Analysis
6 -
Power Automate
6 -
Data Visualization
6 -
Python
6 -
Power BI REST API
6 -
Auto ML
6 -
powerbi
5 -
service
5 -
Power BI PowerShell
5 -
Machine Learning
5 -
Income Statement
5 -
Dax studio
5 -
Power BI Goals
4 -
Desktop
4 -
PowerShell
4 -
Bookmarks
4 -
Line chart
4 -
Group By
4 -
community
4 -
RLS
4 -
M language
4 -
Paginated Reports
4 -
External tool
4 -
calendar
3 -
Gateways
3 -
Webinar
3 -
R
3 -
M Query
3 -
R visual
3 -
CALCULATE
3 -
Reports
3 -
PowerApps
3 -
Tips and Tricks
3 -
Data Science
3 -
Azure
3 -
Data model
3 -
Conditional Formatting
3 -
Life Sciences
3 -
Visualisation
3 -
M code
3 -
Administration
3 -
Visuals
3 -
SQL Server 2017 Express Edition
3 -
R script
3 -
Aggregation
3 -
Support insights.
2 -
construct list
2 -
Formatting
2 -
Weekday Calendar
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 -
Tabular Editor
2 -
SharePoint
2 -
Power BI Installation and Updates
2 -
How Things Work
2 -
Transform data
2 -
Healthcare
2 -
rank
2 -
ladataweb
2 -
Troubleshooting
2 -
Date DIFF
2 -
Incremental Refresh
2 -
Power BI & Power Apps
2 -
Random numbers
2 -
Day of the Week
2 -
Number Ranges
2 -
Query Plans
2 -
Language M
2 -
Custom Visual
2 -
VLOOKUP
2 -
pivot
2 -
calculated column
2 -
M
2 -
hierarchies
2 -
Power BI Anniversary
2 -
Power BI Premium Per user
2 -
inexact
2 -
Date Comparison
2 -
Split
2 -
Forecasting
2 -
REST API
2 -
Editor
2 -
Reverse
2 -
measure
2 -
Microsoft-flow
2 -
Paginated Report Builder
2 -
Working with Non Standatd Periods
2 -
powerbi.tips
2 -
Custom function
2 -
PUG
2 -
Custom Measures
2 -
Filtering
2 -
Row and column conversion
2 -
Python script
2 -
Nulls
2 -
DVW Analytics
2 -
Date duration
2 -
parameter
2 -
Industrial App Store
2 -
Week
2 -
Power BI Premium
1 -
On-premises data gateway
1 -
Binary
1 -
Power BI Connector for SAP
1 -
Sunday
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 -
Dendrogram
1 -
range of values
1 -
activity log
1 -
Decimal
1 -
Charticulator Challenge
1 -
Field parameters
1 -
Announcement
1 -
Features
1 -
domain
1 -
pbiviz
1 -
sport statistics
1 -
Intelligent Plant
1 -
Circular dependency
1 -
GE
1 -
Exchange rate
1 -
Cluster Analysis
1 -
Stacked Area Chart
1 -
union tables
1 -
Number
1 -
Start of Week
1 -
Tips& Tricks
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 -
Label: DAX
1 -
Business Analysis
1 -
Supporting Insight
1 -
rank value
1 -
Synapse
1 -
End of Week
1 -
Tips&Trick
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 -
Power M
1 -
Format DAX
1 -
Custom functions
1 -
accumulative
1 -
DAX&Power Query
1 -
Premium Per User
1 -
GENERATESERIES
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 -
Lineage
1 -
CSV file
1 -
conditional accumulative
1 -
Matrix Subtotal
1 -
Check
1 -
null value
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 -
Keyboard Shortcuts
1 -
Fill Function
1 -
LOOKUPVALUE()
1 -
Tips &Tricks
1 -
Plotly package
1 -
Cumulative Totals
1 -
Report Theme
1 -
Bookmarking
1 -
oracle
1 -
mahak
1 -
pandas
1 -
Networkdays
1 -
Button
1 -
Dataset list
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 -
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 -
Parquet
1 -
Change rows to columns
1 -
remove spaces
1 -
Get row and column totals
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 -
ADLS
1 -
Primary Key
1 -
Microsoft 365 usage analytics data
1 -
Randomly filter
1 -
Week of the Day
1 -
Azure AAD
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 -
Acknowledging
1 -
Postman
1 -
Text.ContainsAny
1 -
Power BI Show
1 -
Get latest sign-in data for each user
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 -
optimization
1 -
Artificial Intelligence
1 -
Map Visual
1 -
Text.ContainsAll
1 -
Tuesday
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 -
Partial group
1 -
Reduce Size
1 -
FBL3N
1 -
Wednesday
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 -
native folded query
1 -
transform table
1 -
UX
1 -
Cell content
1 -
General Ledger
1 -
Thursday
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 -
Friday
1 -
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 -
bulk renaming of columns
1 -
Single Date Picker
1 -
Monday
1 -
PCS
1 -
Saturday
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
- 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