Time intelligence Power BI measure

Question

For my BI report I am trying to show in a visual the values of all opportunities created, historically. The goal is to show the values of an opportunity only for the period it was active. (From the Created date till Close date). Example: If an opportunity was created in June 2023 (2023-06) and was closed in 2024-01, we will see its value only from June 2023 till January 2024.

In present, I have 2 tables: a Date table and a fact table. The fact table includes next columns:

Opportunity's value
Created date
Close date
Expected close date.

Schematically the relationships between the tables look like this:

Thus there is only an active relationship between estimated close date and Date(from Date table) that I want to ignore perhaps.

Despite making a measure and using USERELATIONSHIP function, once I put it in the visual the date values and the calculated measure, it will still take into account that active relationship. What are other steps I can possibly take?

These are the measures I have created.

Step1:

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])

),

0

)

Step2:

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])

),

0

)

Finally Step 3, combining those together:

HistoricalOpportunityExpectedValue =

VAR CurrentDate = MAX(DateDim[Date])

VAR CreatedDate = MAX(ProductPipelineFact[Created On])

VAR CloseDate = MAX(ProductPipelineFact[Close Date])

VAR ExpectedValueByCreatedDate = [ExpectedValueByCreatedDate]

VAR ExpectedValueByCloseDate = [ExpectedValueByCloseDate]

RETURN

IF (

CurrentDate >= CreatedDate && (ISBLANK(CloseDate) || CurrentDate <= CloseDate),

MIN(ExpectedValueByCreatedDate, ExpectedValueByCloseDate),

0

)

shivu-2000 · Answer

Hi&nbsp;AG24&nbsp;I see you are using 3 columns to connect the table, please use only one primary key to join to prevent ambiguity.&nbsp;You can find usefull videos here.https://www.youtube.com/watch?v=cN8AO3_vmlY&amp;list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&nbsp;If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Happy to help!🚀 Attendee Registration Now Open!!!📢 Microsoft Analytics Community Conference: Microsoft Fabric, Copilot and Purview🗓️ November 12th-14th, 2024🌐 Online EventRegister Here

ag24 · Answer

I understand the challange. Unfortunatelly I cannot change the relationships between tables or minimize them. Potentially I can create a separte Date table, not part of the dataset that I am currently using.&nbsp;

grazitti_sapna · Answer

Hi&nbsp;AG24&nbsp;&nbsp;,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:&nbsp;&nbsp;Disable or Ignore the Active Relationship:&nbsp;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:&nbsp;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 DateThis measure activates the Created Date relationship and ignores any active filtering on Estimated Close Date:&nbsp;DAX&nbsp;ExpectedValueByCreatedDate =VAR CurrentDate = MAX(DateDim[Date])VAR CreatedDate = MAX(ProductPipelineFact[Created On])VAR EstimatedValue = [Estimated Value]RETURN&nbsp;&nbsp;&nbsp; IF (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CurrentDate &gt;= CreatedDate,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CALCULATE(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; EstimatedValue,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; USERELATIONSHIP(DateDim[Date], ProductPipelineFact[Created On]),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; REMOVEFILTERS(DateDim)&nbsp; -- Clears any other date filtering&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp; )Measure for Value by Actual Close DateThis measure activates the Actual Close Date relationship to ensure the opportunity’s value stops at the close date:&nbsp;DAX&nbsp;ExpectedValueByCloseDate =VAR CurrentDate = MAX(DateDim[Date])VAR CloseDate = MAX(ProductPipelineFact[Close Date])VAR EstimatedValue = [Estimated Value]RETURN&nbsp;&nbsp;&nbsp; IF (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ISBLANK(CloseDate) || CurrentDate &lt;= CloseDate,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CALCULATE(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; EstimatedValue,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; USERELATIONSHIP(DateDim[Date], ProductPipelineFact[Close Date]),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; REMOVEFILTERS(DateDim)&nbsp; -- Clears any other date filtering&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp; )Combined MeasureThis measure combines both Created Date and Actual Close Date measures to show the opportunity’s value only when it is active:&nbsp;DAX&nbsp;HistoricalOpportunityExpectedValue =VAR CurrentDate = MAX(DateDim[Date])VAR CreatedDate = MAX(ProductPipelineFact[Created On])VAR CloseDate = MAX(ProductPipelineFact[Close Date])RETURN&nbsp;&nbsp;&nbsp; IF (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CurrentDate &gt;= CreatedDate &amp;&amp; (ISBLANK(CloseDate) || CurrentDate &lt;= CloseDate),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; MIN(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [ExpectedValueByCreatedDate],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [ExpectedValueByCloseDate]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp; )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!&nbsp;

sangameshks · Answer

AG24&nbsp;Please note: we need to have data model in star schema and i am sharing a supporting document for the same.Link:&nbsp;Understand star schema and the importance for Power BI - Power BI | Microsoft Learn

ag24 · Answer

Unfortunatelly it still takes into account the estimated close date when adding it into visual.&nbsp;

Forum Discussion

Time intelligence Power BI measure

5 Replies

Related content

POWER BI DAX MEASURE

power bi measure

Help with Date intelligence

Time Intelligence Quick Measures Problem

Interesting effects with calendar-based time intelligence