If Statement Aggregation

[Anonymous]

Hi, 

I wrote a measure to calculate job costs utilizing different accounting methods based on time intelligence. My example code is below:

Estimated Final Cost = 

VAR CurrentDate = TODAY()
VAR RigReleaseDate = SELECTEDVALUE('MDM WELL'[RIG_RELEASE_DATE])
VAR FracEndDate = SELECTEDVALUE('MDM WELL'[FRAC_END_DATE])

VAR EstimatedFinalCost =
    IF( CurrentDate - RigReleaseDate > 180, [Accounting Actual],
        IF(OR(CurrentDate - RigReleaseDate <= 180, CurrentDate - FracEndDate <= 180), [Field Estimate Amount], [AFE Amount])
    )
VAR Result = EstimatedFinalCost / [Adjusted Lateral Length]
RETURN
    Result
        

This works at the level of the entity but when I aggregate to a higher grouping, the measure doesn't appear to know how to iterate between child assets with varying Date Fields (i.e. RigReleaseDate). Below is an example matrix visual using the measure. I am curious how the measure is calculating 66 days between CurrentDate and RigReleaseDate. It needs to iterate through each child and run the IF statement at that level for the aggregation to work correctly. As you can see, a grouping can have several child rows with varying IF statement evaluations.

[v-yiruan-msft]

Hi @Anonymous ,

Could you please provide some sample data and the formulas of measures applied in measure "Estimated Final Cost" just as suggested by @lbendlin in order to make troubleshooting? Also please provide the correct results which should display on visual.

In addition, please try to create a new measure as below to replace the measure "Estimated Final Cost" on the visual and check if it can return the correct values...

Measure = SUMX ( VALUES ( 'MDM WELL'[Formation] ), [Estimated Final Cost] )

Best Regards

Rena

[Anonymous]

@v-yiruan-msft 

That measure didn't provide a different answer. It definitely looks like it is evaluating the date logic using the minimum returned valued from the children.

I can try and provide some sample data though. 

[v-yiruan-msft]

Hi @Anonymous ,

Thanks for your reply. Could you please provide some sample data for the following tables and existed relationships among them? It is better if you can provide your sample pbix file(exclude sensitive data). Thank you.

vwSpotfire_CAPEX

Expense Classification Bridge

AFE_Budget_All

vwSpotfire_CAPEX_Estimated_Expense

MDM WELL

Best Regards

Rena

[lbendlin]

You are not showing enough details. The definitions of 

[Field Estimate Amount]
[AFE Amount]
[Adjusted Lateral Length]

may or may not impact your calculations. 

[Anonymous]

No Problem

Accounting Actual =
CALCULATE (
    SUM ( vwSpotfire_CAPEX[SumAmount] ),
    USERELATIONSHIP ( vwSpotfire_CAPEX[CategoryName], 'Expense Classification Bridge'[CategoryName] )
)

AFE Amount =
CALCULATE (
    SUM ( AFE_Budget_All[AFEBudget] ),
    USERELATIONSHIP ( AFE_Budget_All[CategoryName], 'Expense Classification Bridge'[CategoryName] )
)

Field Estimate Amount =
CALCULATE (
    SUM ( vwSpotfire_CAPEX_Estimated_Expense[EstimatedExpenses] ),
    USERELATIONSHIP ( vwSpotfire_CAPEX_Estimated_Expense[CategoryName], 'Expense Classification Bridge'[CategoryName] )
)

Adjusted Lateral Length = CALCULATE(SUM('MDM WELL'[Lateral Length]))