Forum Discussion

PBrainNWH's avatar
PBrainNWH
Icon for Helper II rankHelper II
2 years ago
Solved

Calculation help (if two columns are not 0 then count)

I'm trying to get a count of records where both FY23 Donations and FY24 Donation <>0, but my calc keeps returning (blank).

CALC:

rReturned in FY2024 =
CALCULATE(
    DISTINCTCOUNT('Opportunity Contact Role'[ContactId]),
    'Opportunity Contact Role'[FY23 Donations] <> 0,
    'Opportunity Contact Role'[FY24 Donations] <> 0
)
 
Both FY23 Donations and FY24 Donations are custom columns:

if [Close_Date__c] > #date(2022, 6, 30) and [Close_Date__c] < #date(2023, 7, 1) then [Amount__c] else 0
 
This is what the data looks like in a table:

And maybe there's a better way to count ids of donors that donated in both fy23 and fy24? I'm stumped.

 

Thanks.

 
 
Need Help
  • DataNinja777's avatar
    DataNinja777
    2 years ago

    Hi PBrainNWH ,

     

    What about replacing the comma with double ampersand (&&)? 

     

     In a copy-pasteable format:

    rReturned in FY2024 =
CALCULATE (
    DISTINCTCOUNT ( 'Opportunity Contact Role'[ContactId] ),
    'Opportunity Contact Role'[FY23 Donations] <> 0
        && 'Opportunity Contact Role'[FY24 Donations] <> 0
)

    Best regards,

2 Replies

  • DataNinja777's avatar
    DataNinja777
    Icon for Super User rankSuper User
    2 years ago

    Hi PBrainNWH ,

     

    What about replacing the comma with double ampersand (&&)? 

     

     In a copy-pasteable format:

    rReturned in FY2024 =
CALCULATE (
    DISTINCTCOUNT ( 'Opportunity Contact Role'[ContactId] ),
    'Opportunity Contact Role'[FY23 Donations] <> 0
        && 'Opportunity Contact Role'[FY24 Donations] <> 0
)

    Best regards,

  • PBrainNWH's avatar
    PBrainNWH
    Icon for Helper II rankHelper II
    2 years ago

    Thanks! I actually was able to create this:

    FY24 Status =
    IF(
    'Donations by Customer by FY'[2023 Donations] <> 0 && 'Donations by Customer by FY'[2024 Donations] <> 0,
    "Retained",
    IF(
    'Donations by Customer by FY'[2023 Donations] = 0 && 'Donations by Customer by FY'[2024 Donations] <> 0,
    "New424",
    "Lapsed"
    )
    )

    I can now count the statuses.