Cumulated number of products with two date columns

[Krumbo1909]

Hello,

I hope this post finds you well. I am experiencing a challenge in creating a visualization to track the point of time by which products are planned to be eliminated compared to the current status—essentially, a target/current comparison.

Goal:

Create a line chart representing the cumulative number of products over time, with the X-axis depicting time and the Y-axis depicting the number of products. I aim to have two lines showing the cumulative count of products (target/current) up to the displayed date.

Approach:

I created a DateTable and established an active relationship to Date 1 and a passive relationship to Date 2. The listed measures below were developed subsequently. When verifying the measures in a table using the date from the DateTable, the first three measures align well, but the ‘Real_Count’ measure reflects incorrect values. The ‘Real_Count’ measure should depict the cumulative frequency up to a date but is showing incorrect values. I suspect the issue lies in the passive relationship to the DateTable.

Example Dataset

| Product | category.       | Elimination | Date1          | Date2.        |
|---------|----------------|--------------|------------   |--------------|
| P1.        | category 3    |                     | 2023-09-01 | 2023-09-15 |
| P2.        | category 2    | x                  | 2023-09-05 | 2023-09-10 |
| P3.        | category 3    |                     | 2023-09-10 | 2023-09-20 |

Measures:

CountProductPlan = CALCULATE(
COUNT(Dataframe[Product]),
FILTER(
Dataframe,
Dataframe[category] = "category 3" && Dataframe[Elimination] <> "x"
),
NOT(ISBLANK(Dataframe[Date1]))
)

CountProductReal = CALCULATE(
[CountProductPlan],
NOT(ISBLANK(Dataframe[Date2])),
USERELATIONSHIP(DateTable[Date], Dataframe[Date2])
)

Plan_Count = CALCULATE(
[CountProductPlan],
FILTER(
ALL(Dataframe),
Dataframe[Date1] <= MAX(Dataframe[Date1])
)
)

Real_Count = CALCULATE(
[CountProductReal],
FILTER(
ALL(Dataframe),
Dataframe[Date2] <= MAX(Dataframe[Date2])
),
USERELATIONSHIP(DateTable[Date], Dataframe[Date2])
)

I would sincerely appreciate any guidance or suggestions on resolving this discrepancy in the ‘Real_Count’ measure.

Thanks in advance for your valuable input!

Best Regards

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...