About

Imported from ECNU-ICALK/AutoSkill (SkillBank/ConvSkill/english_gpt3.5_8_GLM4.7/dax-promo-period-analysis-and-volume-calculation/SKILL.md). Install upstream with npx skills add ECNU-ICALK/AutoSkill --skill dax-promo-period-analysis-and-volume-calculation. Copyright stays with the author.

DAX Promo Period Analysis and Volume Calculation

Name: DAX Promo Period Analysis and Volume Calculation
Author: ECNU-ICALK

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

Multiple Promo Periods: Each combination of Client and Product may have multiple distinct promo periods. The logic must account for this.
Null Handling: Rows with empty StartDate or EndDate represent non-promo sales and must be handled accordingly (treated as regular volume).
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.
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.
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

Use it

Copy one of these into your project. Installing also returns the manifest and these snippets.

yaml

targets:
  - https://api.opensmartroute.ai/api/v1/registry/ecnu-icalk-autoskill-dax-promo-period-analysis-and-volum-ab12a4/manifest   # or paste the manifest below

Manifest

An Open Capability Manifest: the router reads it to know what this does, what it costs and when to pick it.

ecnu-icalk-autoskill-dax-promo-period-analysis-and-volum-ab12a4.ocm.jsonjson

{
  "ocm": "1",
  "id": "ecnu-icalk-autoskill-dax-promo-period-analysis-and-volum-ab12a4",
  "kind": "skill",
  "name": "DAX Promo Period Analysis and Volume Calculation",
  "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.",
  "publisher": "ECNU-ICALK",
  "version": "1.0.0",
  "capabilities": {
    "domains": [
      "data_analysis"
    ],
    "tags": [
      "skill-md",
      "dax",
      "power-bi",
      "sales-analysis",
      "promo-periods",
      "date-logic",
      "github"
    ],
    "languages": [
      "en"
    ]
  },
  "quality_prior": 0.6,
  "examples": [
    "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."
  ],
  "primary": false,
  "metadata": {
    "source": {
      "provider": "github",
      "repository": "https://github.com/ECNU-ICALK/AutoSkill",
      "path": "SkillBank/ConvSkill/english_gpt3.5_8_GLM4.7/dax-promo-period-analysis-and-volume-calculation/SKILL.md",
      "ref": "94c47ca488d4ba4117d20272e66d49b9877e68cf",
      "url": "https://github.com/ECNU-ICALK/AutoSkill/blob/94c47ca488d4ba4117d20272e66d49b9877e68cf/SkillBank/ConvSkill/english_gpt3.5_8_GLM4.7/dax-promo-period-analysis-and-volume-calculation/SKILL.md",
      "key": "ECNU-ICALK/AutoSkill/SkillBank/ConvSkill/english_gpt3.5_8_GLM4.7/dax-promo-period-analysis-and-volume-calculation/SKILL.md"
    }
  },
  "instructions": "# DAX Promo Period Analysis and Volume Calculation\n\nDevelops 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.\n\n## Prompt\n\n# Role & Objective\nYou 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 c",
  "cost": {
    "context_tokens": 617
  }
}

Fetch it by URL: GET /api/v1/registry/ecnu-icalk-autoskill-dax-promo-period-analysis-and-volum-ab12a4/manifest?version=1.0.0

Reviews

Star ratings from people who tried it. One review per account; edit yours any time.

No reviews yet. Install it, try it, and be the first to rate it.