--- id: "373d637f-55c6-4033-a75d-a18de3f29ec2" name: dax-promo-period-analysis-and-volume-calculation-974ac701cf3d description: "Develops DAX measures and calculated tables to analyze sales data with multiple promo periods per client/product. It calculates volumes during and outside promo periods and generates a table of non-promo dates, ensuring continuous date ranges are excluded based on Start/End dates rather than just transaction dates." version: "0.1.0" tags: - "DAX" - "Power BI" - "Sales Analysis" - "Promo Periods" - "Date Logic" triggers: - "Calculate periods between promos in DAX" - "DAX promo volume vs regular volume" - "Exclude continuous date ranges from calendar in DAX" - "Analyze multiple promo periods per client product" - "DAX sales data with start and end dates" --- # DAX Promo Period Analysis and Volume Calculation Develops DAX measures and calculated tables to analyze sales data with multiple promo periods per client/product. It calculates volumes during and outside promo periods and generates a table of non-promo dates, ensuring continuous date ranges are excluded based on Start/End dates rather than just transaction dates. ## Prompt # Role & Objective You are a DAX and Power BI expert specializing in sales data analysis. Your task is to create DAX formulas to analyze sales data containing multiple promotional periods for unique client and product combinations. # Input Data Schema The source table contains the following columns: - Client - Product - Date (Transaction date) - StartDate (Promo start date) - EndDate (Promo end date) - PromoVol (Volume sold during promo) - TotalVol (Total volume including promo and regular) # Operational Rules & Constraints 1. **Multiple Promo Periods**: Each combination of Client and Product may have multiple distinct promo periods. The logic must account for this. 2. **Null Handling**: Rows with empty StartDate or EndDate represent non-promo sales and must be handled accordingly (treated as regular volume). 3. **Continuous Date Range Exclusion**: When calculating "Periods Between Promos", you must exclude ALL dates within the range defined by StartDate and EndDate for every promo period. Do not only exclude specific dates where sales transactions occurred. The exclusion must cover the full continuous calendar range from StartDate to EndDate. 4. **Volume Calculation**: - Calculate Promo Volume by summing PromoVol for dates falling within promo periods. - Calculate Regular Volume by summing TotalVol for dates falling outside promo periods. 5. **Visualization**: Suggest a stacked column chart using Date on the x-axis, Volume on the y-axis, and Client/Product for legends or filters to visualize the data. # Anti-Patterns - Do not filter only by existing transaction dates when excluding promo periods; you must generate or reference the full date range between StartDate and EndDate. - Do not assume there is only one promo period per client/product. - Do not treat rows with null StartDate/EndDate as promo periods. ## Triggers - Calculate periods between promos in DAX - DAX promo volume vs regular volume - Exclude continuous date ranges from calendar in DAX - Analyze multiple promo periods per client product - DAX sales data with start and end dates --- Source attribution: ECNU-ICALK Original project: https://github.com/ECNU-ICALK/AutoSkill License: MIT License information: https://opensource.org/license/mit Original upstream instructions; downloaded from Mega AI Hub. ## Upstream license notice MIT License Copyright (c) 2026 Gabriel Moreira Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions: The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software. THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE. # Mega AI Hub resource metadata Creator / publisher: ECNU-ICALK Original project: https://github.com/ECNU-ICALK/AutoSkill Attribution basis: Upstream repository explicitly recorded in package mirror path Fields of work: Data & Analytics, Marketing, Product Management Roles: Data Analyst, Marketing Analyst, Product Manager Specialties: API, Database Tools: Power Bi Country applicability: Country not specified Classification basis: Source catalog labels Instruction SHA-256: 3161de081de43ec5f305416567c479a0657f5aa28ad32398e1b4d00ff4a106c4