DAX Patterns
Second Edition
1st Edition
Published on 10. August 2020
418 pages
978-1-7353652-1-3 (ISBN)
Description
A pattern is a general, reusable solution to a frequent or common challenge. This book is the second edition of the most comprehensive collection of ready-to-use solutions in DAX, that you can use in Microsoft Power BI, Analysis Services Tabular, and Power Pivot for Excel.
The book includes the following patterns: Time-related calculations, Standard time-related calculations, Month-related calculations, Week-related calculations, Custom time-related calculations, Comparing different time periods, Semi-additive calculations, Cumulative total, Parameter table, Static segmentation, Dynamic segmentation, ABC classification, New and returning customers, Related distinct count, Events in progress, Ranking, Hierarchies, Parent-child hierarchies, Like-for-like comparison, Transition matrix, Survey, Basket analysis, Currency conversion, Budget.
More details
Content
- Intro
- Introduction
- Why we published this book
- How to use this book
- Prerequisites
- Acknowledgments
- Chapter 1 Time-related calculations
- Chapter 2 Standard time-related calculations
- Introduction to time intelligence calculations
- What are standard DAX time intelligence functions
- Disabling the Auto Date/Time
- Limitations of standard time intelligence functions
- Building a Date table
- Controlling the visualization in future dates
- Naming convention
- Computing period-to-date totals
- Year-to-date total
- Quarter-to-date total
- Month-to-date total
- Computing period-over-period growth
- Year-over-year growth
- Quarter-over-quarter growth
- Month-over-month growth
- Period-over-period growth
- Computing period-to-date growth
- Year-over-year-to-date growth
- Quarter-over-quarter-to-date growth
- Month-over-month-to-date growth
- Comparing period-to-date with previous full period
- Year-to-date over the full previous year
- Quarter-to-date over full previous quarter
- Month-to-date over full previous month
- Using moving annual total calculations
- Moving annual total
- Moving annual total growth
- Moving averages
- Moving average 30 days
- Moving average 3 months
- Moving average 1 year
- Filtering other date attributes
- Chapter 3 Month-related calculations
- Introduction to month-related time intelligence calculations
- Building a Date table
- Naming convention
- Computing period-to-date totals
- Year-to-date total
- Quarter-to-date total
- Computing period-over-period growth
- Year-over-year growth
- Quarter-over-quarter growth
- Month-over-month growth
- Period-over-period growth
- Computing period-to-date growth
- Year-over-year-to-date growth
- Quarter-over-quarter-to-date growth
- Comparing period-to-date with a previous full period
- Year-to-date over the full previous year
- Quarter-to-date over full previous quarter
- Using moving annual total calculations
- Moving annual total
- Moving annual total growth
- Moving averages
- Moving average 3 months
- Moving average 1 year
- Managing years with more than 12 months
- Chapter 4 Week-related calculations
- Introduction to week-related time intelligence calculations
- Building a Date table
- Understanding filter-safe columns
- Controlling the visualization in future dates
- Naming convention
- Computing period-to-date totals
- Year-to-date total
- Quarter-to-date total
- Month-to-date total
- Week-to-date total
- Computing period-over-period growth
- Year-over-year growth
- Quarter-over-quarter growth
- Week-over-week growth
- Period-over-period growth
- Computing period-to-date growth
- Year-over-year-to-date growth
- Quarter-over-quarter-to-date growth
- Week-over-week-to-date growth
- Comparing period-to-date with previous full period
- Year-to-date over the full previous year
- Quarter-to-date over the full previous quarter
- Week-to-date over the full previous week
- Using moving annual total calculations
- Moving annual total
- Moving annual total growth
- Moving averages
- Moving average 4 weeks
- Moving average 1 quarter
- Moving average 1 year
- Chapter 5 Custom time-related calculations
- Introduction to custom time intelligence calculations
- Building a Date table
- Understanding filter-safe columns
- Controlling the visualization on future dates
- Naming convention
- Computing period-to-date totals
- Year-to-date total
- Quarter-to-date total
- Month-to-date total
- Computing period-over-period growth
- Year-over-year growth
- Quarter-over-quarter growth
- Month-over-month growth
- Period-over-period growth
- Computing period-to-date growth
- Year-over-year-to-date growth
- Quarter-over-quarter-to-date growth
- Month-over-month-to-date growth
- Comparing period-to-date with a previous full period
- Year-to-date over the full previous year
- Quarter-to-date over the full previous quarter
- Month-to-date over the full previous month
- Using moving annual total calculations
- Moving annual total
- Moving annual total growth
- Moving averages
- Moving average 30 days
- Moving average 1 year
- Chapter 6 Comparing different time periods
- Pattern description
- Chapter 7 Semi-additive calculations
- Introduction
- First and last date
- First and last date with data
- First and last date by customer
- Opening and closing balance
- Growth in period
- Chapter 8 Cumulative total
- Basic scenario
- Cumulative total on columns that can be sorted
- Chapter 9 Parameter table
- Changing the scale of a measure
- Multiple independent parameters
- Multiple dependent parameters
- Selecting top N products dynamically
- Chapter 10 Static segmentation
- Basic pattern
- Price ranges by category
- Price ranges on large tables
- Chapter 11 Dynamic segmentation
- Basic pattern
- Clustering by product growth
- Clustering by best status
- Chapter 12 ABC classification
- Static ABC classification
- Snapshot ABC classification
- Dynamic ABC classification
- Finding the ABC class
- Chapter 13 New and returning customers
- Introduction
- Pattern description
- Internal measures
- External measures
- How to use pattern measures
- Internal measures
- New customers
- Lost customers
- Temporarily-lost customers
- Recovered customers
- Returning customers
- Dynamic absolute
- Internal measures
- New customers
- Lost customers
- Temporarily-lost customers
- Recovered customers
- Returning customers
- Generic dynamic pattern (dynamic by category)
- Internal measures
- New customers
- Lost customers
- Temporarily-lost customers
- Recovered customers
- Returning customers
- Snapshot absolute
- Creating the derived snapshot table in DAX
- Chapter 14 Related distinct count
- Pattern description
- Chapter 15 Events in progress
- Definition of events in progress
- Open orders
- Open orders with snapshot
- Chapter 16 Ranking
- Static ranking
- Dynamic ranking
- Showing the top 3 products by category
- Chapter 17 Hierarchies
- Detecting the current level of a hierarchy
- Percentage of parent node
- Chapter 18 Parent-child hierarchies
- Introduction
- Basic Parent-child pattern
- Chart of accounts hierarchy
- Security pattern for a parent-child hierarchy
- Chapter 19 Like-for-like comparison
- Introduction
- Same store sales with snapshot
- Same store sales without snapshot
- Chapter 20 Transition matrix
- Introduction
- Static transition matrix
- Dynamic transition matrix
- Chapter 21 Survey
- Pattern description
- Chapter 22 Basket analysis
- Defining association rules metrics
- #
- # And
- # Total
- # Both
- % Support
- % Confidence
- Lift
- Sample reports
- Basic pattern example
- Optimized pattern example
- Chapter 23 Currency conversion
- Multiple source currencies, single reporting currency
- Single source currency, multiple reporting currencies
- Multiple source currencies, multiple reporting currencies
- Chapter 24 Budget
- Introduction
- The data model
- Business choices
- Allocation based on the previous year
- Dismissed products do not contribute to the allocation
- New products have their own forecast amount
- Products can be dismissed or introduced on a yearly basis
- Forecast allocation
- Showing actuals and forecasts on the same chart
- About the authors
