Dear all,I have a question and I request some support. (I am trying to solve this in Power BI but maybe its a real back-end issue) In Power BI we consume a starschema where different products (Dim_Product) had different purchases in Fact_ProductTransactions.The issue I have, some products belong to different categories and I have to calculate the leadtime of a product in each category. OrderIDNameGroup-BikeGroup-CarGroup-TruckStartDateEndDate1A1Product A1Bike Truck01/01/202101/04/20262A2Product A2Bike Truck01/01/202101/04/20263A2.5Product A3Bike Truck01/01/202101/04/20264A3Product A4Bike Truck03/01/202101/04/20265A4Product A5Bike 03/01/202101/04/20266A5Product A6Bike Truck01/01/202101/04/20267A6Product A7Bike Truck01/01/202101/04/20269A7Product A8Bike Truck01/01/202101/04/202610A8Product A9Bike 01/01/202102/04/202611A9Product A10Bike Truck02/01/202101/04/202613A58Product A11Bike Truck04/11/202401/04/202614A59Product A12Bike Truck04/11/202427/03/202615A60Product A13Bike Truck14/11/202431/03/202616A61Product A14Bike Truck14/11/202401/04/202617A62Product A15Bike Truck12/12/202401/04/202618A11Product A16BikeCar 04/01/202131/03/202619A12Product A17BikeCar 04/01/202131/03/202620A13Product A18BikeCar 04/01/202131/03/202621A53Product A19Bike Truck12/01/202131/03/202622A52Product A20Bike Truck04/01/202131/03/202623A55Product A21 Truck31/03/202231/03/202624A56Product A22BikeCar 30/06/202227/03/202625A14Product A23BikeCar 03/01/202131/03/202630A19Product A24BikeCar 04/01/202131/03/202631A20Product A25Bike Truck04/01/202101/04/202632A21Product A26Bike Truck04/01/202101/04/202633A31Product A27Bike Truck04/01/202102/04/202634A22Product A28BikeCar 03/01/202131/03/202635A23Product A29 Truck04/01/202101/04/202637A25Product A30 Car 04/01/202131/03/202638A26Product A31 Truck03/01/202101/04/202639A27Product A32 Car 04/01/202131/03/202641A49Product A33 Truck22/04/202131/03/202642A29Product A34 Truck04/01/202131/03/202643A30Product A35 Car 04/01/202131/03/202644A32Product A36 Truck04/01/202101/04/2026 You can see that sometimes a category (Bike-Car-Truck) is blank if we would like to:1. Use all three columns as slicer (This I can solve using Field Parameter option)2. Calculate leadtime (DateDiff between start and end date in days) based on the following conditions:a. Blank should be ignoredb. Overalpping start and end date need to be ignored (We dont want double leadtime, if they run at the same time then only take 1 or deduct the overlapping leadtime)c. Gaps between products (If a product starts in a year after the previous product ends) need to be ignored or deducted from total leadtime So far what I though about. Maybe some more scenario's will promt up in the next days as I am analyzing the data and all modelling options.

Hi Hichamas4 You are facing a scenario where products may belong to multiple categories (Bike, Car, Truck), and where each product-category combination has associated date intervals. Your objective is to calculate lead time in days per category while applying three conditions: Categories left blank must be ignored. Overlapping date intervals must not be counted twice. Gaps (periods where no interval exists) must not contribute to the total lead time. This is fundamentally an “interval-union” problem, where multiple date ranges per product and category need to be treated as a single continuous timeline. 1. Recommended Data Modelling Approach Using separate columns for categories (Group-Bike, Group-Car, Group-Truck) complicates the calculations.A more suitable modelling approach is to normalise the categories by creating a bridge table. Proposed structure Dim_ProductProductID, ProductName Bridge_ProductCategoryProductID, Category(One row per category, for example Bike, Car or Truck) Fact_ProductTransactionsProductID, StartDate, EndDate This structure allows analytical filtering by category without dealing with blank columns, and avoids complex conditional logic. Power Query transformation (conceptual steps) Duplicate the raw product table. Unpivot the three category columns. Remove rows where the category value is blank. Produce a table containing only ProductID and Category. This becomes your bridge table linking products to categories. 2. Lead Time Calculation Strategy The most robust method for handling overlapping intervals and gaps is to use a Date dimension and count the number of distinct days for which at least one interval is active. Because a day is counted only once, overlapping intervals do not accumulate duplicate lead-time.Days without an interval automatically represent gaps and are excluded. Required tables Dim_Date Fact_ProductTransactions Dim_Product Bridge_ProductCategory Dim_Category Lead Time Measure (DAX): Leadtime Days := VAR ActiveDates = FILTER( ALL('Dim_Date'[Date]), CALCULATE( COUNTROWS('Fact_ProductTransactions'), 'Dim_Date'[Date] >= 'Fact_ProductTransactions'[StartDate] && 'Dim_Date'[Date] <= 'Fact_ProductTransactions'[EndDate] ) > 0 ) RETURN COUNTROWS(ActiveDates) This measure returns the number of days in which at least one interval is active in the current filter context (Product, Category, etc.). Overlaps and gaps are handled implicitly. 3. When to Move Logic to the Back‑End If the volume of data is high, or if the Date dimension spans many years, the DAX approach may become slow.In that case, it is advisable to consolidate intervals in Power Query or in a SQL back‑end by merging overlapping periods per Product and Category before loading them into the model. Lead time can then be calculated with a simple DAX expression using SUMX over consolidated intervals. 4. Summary of Both Approaches Option A – DAX solution (suitable for moderate volumes) Normalise category columns into a bridge table. Use a Date table. Calculate lead time by counting distinct active days. Option B – Back‑end consolidation (suitable for large volumes) Merge overlapping date intervals per Product and Category before loading to Power BI. Use simple DAX to sum the lengths of consolidated intervals. DISCLAIMER: While I wrote a draft of this answer, I used Copilot to create a longer, more detailed step-by-step description to make it easier to apply.

Forum Discussion

Hichamas4

Frequent Visitor

4 months ago

Solved

Measure/Model calculation support needed

Dear all,

I have a question and I request some support. (I am trying to solve this in Power BI but maybe its a real back-end issue)

In Power BI we consume a starschema where different products (Dim_Product) had different purchases in Fact_ProductTransactions.

The issue I have, some products belong to different categories and I have to calculate the leadtime of a product in each category.

Order	ID	Name	Group-Bike	Group-Car	Group-Truck	StartDate	EndDate
1	A1	Product A1	Bike		Truck	01/01/2021	01/04/2026
2	A2	Product A2	Bike		Truck	01/01/2021	01/04/2026
3	A2.5	Product A3	Bike		Truck	01/01/2021	01/04/2026
4	A3	Product A4	Bike		Truck	03/01/2021	01/04/2026
5	A4	Product A5	Bike			03/01/2021	01/04/2026
6	A5	Product A6	Bike		Truck	01/01/2021	01/04/2026
7	A6	Product A7	Bike		Truck	01/01/2021	01/04/2026
9	A7	Product A8	Bike		Truck	01/01/2021	01/04/2026
10	A8	Product A9	Bike			01/01/2021	02/04/2026
11	A9	Product A10	Bike		Truck	02/01/2021	01/04/2026
13	A58	Product A11	Bike		Truck	04/11/2024	01/04/2026
14	A59	Product A12	Bike		Truck	04/11/2024	27/03/2026
15	A60	Product A13	Bike		Truck	14/11/2024	31/03/2026
16	A61	Product A14	Bike		Truck	14/11/2024	01/04/2026
17	A62	Product A15	Bike		Truck	12/12/2024	01/04/2026
18	A11	Product A16	Bike	Car		04/01/2021	31/03/2026
19	A12	Product A17	Bike	Car		04/01/2021	31/03/2026
20	A13	Product A18	Bike	Car		04/01/2021	31/03/2026
21	A53	Product A19	Bike		Truck	12/01/2021	31/03/2026
22	A52	Product A20	Bike		Truck	04/01/2021	31/03/2026
23	A55	Product A21			Truck	31/03/2022	31/03/2026
24	A56	Product A22	Bike	Car		30/06/2022	27/03/2026
25	A14	Product A23	Bike	Car		03/01/2021	31/03/2026
30	A19	Product A24	Bike	Car		04/01/2021	31/03/2026
31	A20	Product A25	Bike		Truck	04/01/2021	01/04/2026
32	A21	Product A26	Bike		Truck	04/01/2021	01/04/2026
33	A31	Product A27	Bike		Truck	04/01/2021	02/04/2026
34	A22	Product A28	Bike	Car		03/01/2021	31/03/2026
35	A23	Product A29			Truck	04/01/2021	01/04/2026
37	A25	Product A30		Car		04/01/2021	31/03/2026
38	A26	Product A31			Truck	03/01/2021	01/04/2026
39	A27	Product A32		Car		04/01/2021	31/03/2026
41	A49	Product A33			Truck	22/04/2021	31/03/2026
42	A29	Product A34			Truck	04/01/2021	31/03/2026
43	A30	Product A35		Car		04/01/2021	31/03/2026
44	A32	Product A36			Truck	04/01/2021	01/04/2026

You can see that sometimes a category (Bike-Car-Truck) is blank if we would like to:

1. Use all three columns as slicer (This I can solve using Field Parameter option)

2. Calculate leadtime (DateDiff between start and end date in days) based on the following conditions:

a. Blank should be ignored

b. Overalpping start and end date need to be ignored (We dont want double leadtime, if they run at the same time then only take 1 or deduct the overlapping leadtime)

c. Gaps between products (If a product starts in a year after the previous product ends) need to be ignored or deducted from total leadtime

So far what I though about. Maybe some more scenario's will promt up in the next days as I am analyzing the data and all modelling options.

Need Help

Zanqueta
4 months ago
Hi Hichamas4

You are facing a scenario where products may belong to multiple categories (Bike, Car, Truck), and where each product-category combination has associated date intervals. Your objective is to calculate lead time in days per category while applying three conditions:

Categories left blank must be ignored.

Overlapping date intervals must not be counted twice.

Gaps (periods where no interval exists) must not contribute to the total lead time.

This is fundamentally an “interval-union” problem, where multiple date ranges per product and category need to be treated as a single continuous timeline.

1. Recommended Data Modelling Approach

Using separate columns for categories (Group-Bike, Group-Car, Group-Truck) complicates the calculations.
A more suitable modelling approach is to normalise the categories by creating a bridge table.
Proposed structure

Dim_Product
ProductID, ProductName

Bridge_ProductCategory
ProductID, Category
(One row per category, for example Bike, Car or Truck)

Fact_ProductTransactions
ProductID, StartDate, EndDate
This structure allows analytical filtering by category without dealing with blank columns, and avoids complex conditional logic.
Power Query transformation (conceptual steps)

Duplicate the raw product table.

Unpivot the three category columns.

Remove rows where the category value is blank.

Produce a table containing only ProductID and Category.

This becomes your bridge table linking products to categories.

2. Lead Time Calculation Strategy

The most robust method for handling overlapping intervals and gaps is to use a Date dimension and count the number of distinct days for which at least one interval is active.
Because a day is counted only once, overlapping intervals do not accumulate duplicate lead-time.
Days without an interval automatically represent gaps and are excluded.
Required tables

Dim_Date

Fact_ProductTransactions

Dim_Product

Bridge_ProductCategory

Dim_Category

Lead Time Measure (DAX):

Leadtime Days :=
VAR ActiveDates =
    FILTER(
        ALL('Dim_Date'[Date]),
        CALCULATE(
            COUNTROWS('Fact_ProductTransactions'),
            'Dim_Date'[Date] >= 'Fact_ProductTransactions'[StartDate]
                && 'Dim_Date'[Date] <= 'Fact_ProductTransactions'[EndDate]
        ) > 0
    )
RETURN
    COUNTROWS(ActiveDates)

This measure returns the number of days in which at least one interval is active in the current filter context (Product, Category, etc.). Overlaps and gaps are handled implicitly.

3. When to Move Logic to the Back‑End

If the volume of data is high, or if the Date dimension spans many years, the DAX approach may become slow.
In that case, it is advisable to consolidate intervals in Power Query or in a SQL back‑end by merging overlapping periods per Product and Category before loading them into the model.
Lead time can then be calculated with a simple DAX expression using SUMX over consolidated intervals.

4. Summary of Both Approaches

Option A – DAX solution (suitable for moderate volumes)

Normalise category columns into a bridge table.

Use a Date table.

Calculate lead time by counting distinct active days.

Option B – Back‑end consolidation (suitable for large volumes)

Merge overlapping date intervals per Product and Category before loading to Power BI.

Use simple DAX to sum the lengths of consolidated intervals.

DISCLAIMER: While I wrote a draft of this answer, I used Copilot to create a longer, more detailed step-by-step description to make it easier to apply.

4 Replies

Zanqueta
Super User
4 months ago
Hi Hichamas4

You are facing a scenario where products may belong to multiple categories (Bike, Car, Truck), and where each product-category combination has associated date intervals. Your objective is to calculate lead time in days per category while applying three conditions:

Categories left blank must be ignored.

Overlapping date intervals must not be counted twice.

Gaps (periods where no interval exists) must not contribute to the total lead time.

This is fundamentally an “interval-union” problem, where multiple date ranges per product and category need to be treated as a single continuous timeline.

1. Recommended Data Modelling Approach

Using separate columns for categories (Group-Bike, Group-Car, Group-Truck) complicates the calculations.
A more suitable modelling approach is to normalise the categories by creating a bridge table.
Proposed structure

Dim_Product
ProductID, ProductName

Bridge_ProductCategory
ProductID, Category
(One row per category, for example Bike, Car or Truck)

Fact_ProductTransactions
ProductID, StartDate, EndDate
This structure allows analytical filtering by category without dealing with blank columns, and avoids complex conditional logic.
Power Query transformation (conceptual steps)

Duplicate the raw product table.

Unpivot the three category columns.

Remove rows where the category value is blank.

Produce a table containing only ProductID and Category.

This becomes your bridge table linking products to categories.

2. Lead Time Calculation Strategy

The most robust method for handling overlapping intervals and gaps is to use a Date dimension and count the number of distinct days for which at least one interval is active.
Because a day is counted only once, overlapping intervals do not accumulate duplicate lead-time.
Days without an interval automatically represent gaps and are excluded.
Required tables

Dim_Date

Fact_ProductTransactions

Dim_Product

Bridge_ProductCategory

Dim_Category

Lead Time Measure (DAX):

Leadtime Days :=
VAR ActiveDates =
    FILTER(
        ALL('Dim_Date'[Date]),
        CALCULATE(
            COUNTROWS('Fact_ProductTransactions'),
            'Dim_Date'[Date] >= 'Fact_ProductTransactions'[StartDate]
                && 'Dim_Date'[Date] <= 'Fact_ProductTransactions'[EndDate]
        ) > 0
    )
RETURN
    COUNTROWS(ActiveDates)

This measure returns the number of days in which at least one interval is active in the current filter context (Product, Category, etc.). Overlaps and gaps are handled implicitly.

3. When to Move Logic to the Back‑End

If the volume of data is high, or if the Date dimension spans many years, the DAX approach may become slow.
In that case, it is advisable to consolidate intervals in Power Query or in a SQL back‑end by merging overlapping periods per Product and Category before loading them into the model.
Lead time can then be calculated with a simple DAX expression using SUMX over consolidated intervals.

4. Summary of Both Approaches

Option A – DAX solution (suitable for moderate volumes)

Normalise category columns into a bridge table.

Use a Date table.

Calculate lead time by counting distinct active days.

Option B – Back‑end consolidation (suitable for large volumes)

Merge overlapping date intervals per Product and Category before loading to Power BI.

Use simple DAX to sum the lengths of consolidated intervals.

DISCLAIMER: While I wrote a draft of this answer, I used Copilot to create a longer, more detailed step-by-step description to make it easier to apply.
danextian
Super User
4 months ago
Hi Hichamas4

Using the same sample data provided, please show your expected result and the explanation behind.

Note: I had to emphasize using the same sample data provided as there were users who provided the expected result but used a different data.
v-hashadapu
Community Support
4 months ago
Hi Hichamas4 , Thank you for reaching out to the Microsoft Community Forum.

We find the answer shared by Zanqueta is appropriate. Can you please confirm if the solution worked for you. It will help others with similar issues find the answer easily.
Thank you Zanqueta for your valuable response.
v-hashadapu
Community Support
4 months ago
Hi Hichamas4 , Hope you are doing well. Kindly let us know if the issue has been resolved or if further assistance is needed. Your input could be helpful to others in the community.

Forum Discussion

Measure/Model calculation support needed

1. Recommended Data Modelling Approach

Proposed structure

Power Query transformation (conceptual steps)

2. Lead Time Calculation Strategy

Required tables

Lead Time Measure (DAX):

3. When to Move Logic to the Back‑End

4. Summary of Both Approaches

4 Replies

1. Recommended Data Modelling Approach

Proposed structure

Power Query transformation (conceptual steps)

2. Lead Time Calculation Strategy

Required tables

Lead Time Measure (DAX):

3. When to Move Logic to the Back‑End

4. Summary of Both Approaches

Related content

MdxScript(Model) Calculation error in measure

DAX measures Clean-up in data model?

MdxScript(Model) (37,5) Calculation error in measure

Use measures from another semantic model

Using measures from multiple semantic models