Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

If Statement Aggregation

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.
 
 
Need Help

5 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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]))
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      Anonymous 

       

      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. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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