Measure/Model calculation support needed

[Hichamas4]

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.

[v-hashadapu]

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.

[danextian]

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.

[Zanqueta]

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.