Forum Discussion

Rsanjuan's avatar
Rsanjuan
Advocate III
9 years ago

If - Then Calculation Problems

Hi,   I am having trouble creating an If - Then calculation and was hoping someone here can help.     I am trying to calculate the current year revenue and previous year revenue for different bus...
Need Help
v-ljerr-msft's avatar
v-ljerr-msft
Microsoft Employee
9 years ago

Hi Rsanjuan,

 

Could you try using the formula below to see if it works in your scenario? :smileyhappy:

PY Revenue (Quant) =
CALCULATE (
    SUM ( 'Master 2'[Revenue] ),
    FILTER (
        ALL ( 'Master 2'[Job.Invoice_Date__c] ),
        'Master 2'[Job.Invoice_Date__c]
            <= DATE ( YEAR ( TODAY () ) - 1, MONTH ( TODAY () ), DAY ( TODAY () ) + 1 )
            && 'Master 2'[Job.Job_Phase__c] = "Closed"
            && YEAR ( 'Master 2'[Job.Invoice_Date__c] )
                = YEAR ( TODAY () ) - 1
    )
)

PY Revenue (Qual) =
CALCULATE (
    SUM ( 'Master 2'[Revenue] ),
    FILTER (
        ALL ( 'Master 2'[JML.Invoice_Date__c] ),
        'Master 2'[JML.Invoice_Date__c]
            <= DATE ( YEAR ( TODAY () ) - 1, MONTH ( TODAY () ), DAY ( TODAY () ) + 1 )
            && 'Master 2'[Job.Job_Status__c] = "Closed"
            && YEAR ( 'Master 2'[JML.Invoice_Date__c] )
                = YEAR ( TODAY () ) - 1
    )
)

 

Regards

  • Rsanjuan's avatar
    Rsanjuan
    Advocate III
    9 years ago

    v-ljerr-msft

     

    Unfortunately, it didn't work.  :(

     

    I'm still getting a circular dependency.

    • mattbrice's avatar
      mattbrice
      Solution Sage
      9 years ago

      What exactly does circular dependency error message say?

       

      Usually with calculated columns the circular dependency is because of the use of a CALCULATE statement.  CALCULATE transitions all the other columns in the table into the filter context for your calculation - including other calculated columns.  So you most likely have the new columns trying to transition each other into their respective filter contexts which is causing the error.  Chicken of the egg syndrome:  DAX can't resolve one calculated column before the other which is needed to complete calculation. To combat that, I usually use ALLEXCEPT() to remove the "other" column from the current columns context transition.