cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Rsanjuan
Helper IV
Helper IV

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 business units.  They utilize different date fields as well as a different filter.

 

I was able to get it working for the current year.  First, created two measures:

 

CY Revenue (Quant) = Calculate(sum('Master 2'[Revenue]), 'Master 2'[Job.Job_Phase__c]="Closed", Year('Master 2'[Job.Invoice_Date__c])=Year(Today()))

 

CY Revenue (Qual) = Calculate(sum('Master 2'[Revenue]), 'Master 2'[Job.Job_Status__c]="Closed", Year('Master 2'[JML.Invoice_Date__c])=Year(Today()))

 

Then to populate it in the same column, I created a column:

 

CY Revenue = If(And('Master 2'[Division]="Quant",Year('Master 2'[Job.Invoice_Date__c])=Year(Today())),[CY Revenue (Quant)],[CY Revenue (Qual)])

 

However, when I do the same for Previous Year, I get a circular dependency error.  here are the formulas that I tried to use for previous year:

 

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

 

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

 

PY Revenue = If(And('Master 2'[Division]="Quant",Year('Master 2'[Job.Invoice_Date__c])=Year(Today())-1),[PY Revenue (Quant)],[PY Revenue (Qual)])

 

Any ideas?

 

 

 

 

 

3 REPLIES 3
v-ljerr-msft
Microsoft
Microsoft

Hi @Rsanjuan,

 

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

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

@v-ljerr-msft

 

Unfortunately, it didn't work.  😞

 

I'm still getting a circular dependency.

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.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors