Time intelligence Power BI measure

Hi AG24  ,I think we're running into a bit of a roadblock with the multiple relationships between ProductPipelineFact and DateDim.

To get the historical tracking for opportunities working correctly, we might need to:

• Disable or Ignore the Active Relationship:

   

  • If the Estimated Close Date relationship isn't crucial for other calculations, we can simply deactivate it.
  • Otherwise, we'll need to use REMOVEFILTERS or ALL to ignore it in our measures.
• Create Measures with USERELATIONSHIP:
  •  Use USERELATIONSHIP to activate the Created Date and Actual Close Date relationships when needed.
  • This will help us calculate values based on the correct date ranges.

Measure for Value by Created Date

This measure activates the Created Date relationship and ignores any active filtering on Estimated Close Date:

DAX

ExpectedValueByCreatedDate =
VAR CurrentDate = MAX(DateDim[Date])
VAR CreatedDate = MAX(ProductPipelineFact[Created On])
VAR EstimatedValue = [Estimated Value]

RETURN
    IF (
        CurrentDate >= CreatedDate,
        CALCULATE(
            EstimatedValue,
            USERELATIONSHIP(DateDim[Date], ProductPipelineFact[Created On]),
            REMOVEFILTERS(DateDim)  -- Clears any other date filtering
        ),
        0
    )

Measure for Value by Actual Close Date

This measure activates the Actual Close Date relationship to ensure the opportunity’s value stops at the close date:

DAX

ExpectedValueByCloseDate =
VAR CurrentDate = MAX(DateDim[Date])
VAR CloseDate = MAX(ProductPipelineFact[Close Date])
VAR EstimatedValue = [Estimated Value]

RETURN
    IF (
        ISBLANK(CloseDate) || CurrentDate <= CloseDate,
        CALCULATE(
            EstimatedValue,
            USERELATIONSHIP(DateDim[Date], ProductPipelineFact[Close Date]),
            REMOVEFILTERS(DateDim)  -- Clears any other date filtering
        ),
        0
    )

Combined Measure

This measure combines both Created Date and Actual Close Date measures to show the opportunity’s value only when it is active:

DAX

HistoricalOpportunityExpectedValue =
VAR CurrentDate = MAX(DateDim[Date])
VAR CreatedDate = MAX(ProductPipelineFact[Created On])
VAR CloseDate = MAX(ProductPipelineFact[Close Date])

RETURN
    IF (
        CurrentDate >= CreatedDate && (ISBLANK(CloseDate) || CurrentDate <= CloseDate),
        MIN(
            [ExpectedValueByCreatedDate],
            [ExpectedValueByCloseDate]
        ),
        0
    )

This setup should achieve the result of displaying opportunity values only for the period between Created Date and Actual Close Date, ignoring any effect of the Estimated Close Date relationship.
If I have resolved your question, please consider marking my post as a solution🎉. Thank you!