DAX: SUM an IF statement comparing 2 Measures

[Mark_Holland_GD]

Hi,

I want to sum the results of an expression that uses and IF statement to get the difference between 2 measures. Here is the DAX I'm currently using:

Total Single Giving Donors New (FYTD) = IF([Total Single Giving Donation Amount (FYTD)] > 0 && [Total Single Giving Donation Amount (PFYTD)] <= 0, 1, 0)

When added to a table, it marks, as in the following example:

Highlighted in Yellow are all the times when the statement is true - when a Customer has a value the second column "Total Single Giving Donation Amount (FYTD)" and  no value for the fourth column "Total Single Giving Donation Amount (PFYTD)". This indicates that this person has donated for the first time.

For context, here's one of the Measures from within the expressions:

Total Single Giving Donation Amount (FYTD) = 
CALCULATE([Total Single Giving Donation Amount],
FILTER(ALL('02: Calendar'), '02: Calendar'[Year ID] = MAX('02: Calendar'[Year ID]) && '02: Calendar'[Month ID] <= MAX('02: Calendar'[Month ID])))

I want to be able to sum each of these to get an overall total that I can use in a card visual. 

Can anyone help with this?

Thanks,

Mark

[Mark_Holland_GD]

Hi @Jihwan_Kim ,

This doesn't quite work. It does give me a sum but the number is not what I'm expecting. It's showing 1,005 when I'm expecting 2,261.

Perhaps it will help if I show how my measures are all stacked up.

I'm starting with the following measure to work out the Total Opportunity Amount. I'm using USERELATIONSHIP as there are a few Date fields on the Donor Opportunities table:

Total Opportunity Amount = 
CALCULATE(SUM('DONOR OPPORTUNITIES (RAW)'[Opportunity Amount]),
USERELATIONSHIP('DONOR OPPORTUNITIES (RAW)'[Opportunity Closed Date], '02: Calendar'[Dates]))

I then filter this measure to get only the Amount that relates to Single Giving:

Total Single Giving Donation Amount = 
CALCULATE([Total Opportunity Amount], 
'DONOR OPPORTUNITIES (RAW)'[Opportunity Won] = TRUE(),
'DONOR OPPORTUNITIES (RAW)'[Opportunity Bequest] = FALSE(),
'DONOR OPPORTUNITIES (RAW)'[Business Unit, Regular Giving] = FALSE())

Once I have this, I then work out the FYTD value:

Total Single Giving Donation Amount (FYTD) = 
CALCULATE([Total Single Giving Donation Amount],
FILTER(ALL('02: Calendar'), '02: Calendar'[Year ID] = MAX('02: Calendar'[Year ID]) && '02: Calendar'[Month ID] <= MAX('02: Calendar'[Month ID])))

I do the same for PFYTD:

Total Single Giving Donation Amount (PFYTD) = 
CALCULATE([Total Single Giving Donation Amount],
FILTER(ALL('02: Calendar'), '02: Calendar'[Year ID] = MAX('02: Calendar'[Year ID]) -1 && '02: Calendar'[Month ID] <= MAX('02: Calendar'[Month ID])))

Then I do my IF statement from the top of this thread. When I drop this final measure into a table looking at November FYTD, the SUM is not quite what I'm expecting. 

Is there something else I could try?

Mark

[Jihwan_Kim]

Hi,

If it is OK, please share your sample pbix file's link (onedrive, dropbox, or googledrive), and then I can try to look into it with understanding more about your data model.

Thank you.

[Mark_Holland_GD]

Hi @Jihwan_Kim ,

I'm afraid I can't share any of the Data Model. But it's relatively simple. 

• I have 2 tables
  • Calendar and Donor Opportunities, which are linked via Opportunity Closed Date.
• A Supporter can have zero, one or multiple Opportunies on any date
• There are lots of different Opportunity types, I'm only interested in those that are Won and relate to Single Giving - which is the filtering you see on the Total Single Giving Donation Amount measure
• If a Supporter has any value for Amount in the past 12 Months but not the 12 months prior to that, they are considered a New Supporter, which is what this measure is highlighting

Total Single Giving Donors New (FYTD) = IF([Total Single Giving Donation Amount (FYTD)] > 0 && [Total Single Giving Donation Amount (PFYTD)] <= 0, 1, 0)

Does that help?

Mark

[Mark_Holland_GD · Accepted solution]

Hi @Jihwan_Kim ,

I used ChatGPT. It took me a while to figure out the right way to ask the question. But eventually we got there:

Total Single Giving Donors - New (FYTD) = 
COUNTROWS(
    EXCEPT(
        CALCULATETABLE(
            DISTINCT('DONOR OPPORTUNITIES (RAW)'[Supporter ID]),
            'DONOR OPPORTUNITIES (RAW)'[Opportunity Won] = TRUE(),
            'DONOR OPPORTUNITIES (RAW)'[Opportunity Bequest] = FALSE(),
            'DONOR OPPORTUNITIES (RAW)'[Business Unit, Regular Giving] = FALSE(),
            'DONOR OPPORTUNITIES (RAW)'[Supporter ID] <> BLANK(),
            FILTER(
                ALL('02: Calendar'),
                '02: Calendar'[Year ID] = MAX('02: Calendar'[Year ID]) &&
                '02: Calendar'[Month ID] <= MAX('02: Calendar'[Month ID])
            )
        ),
        CALCULATETABLE(
            DISTINCT('DONOR OPPORTUNITIES (RAW)'[Supporter ID]),
            'DONOR OPPORTUNITIES (RAW)'[Opportunity Won] = TRUE(),
            'DONOR OPPORTUNITIES (RAW)'[Opportunity Bequest] = FALSE(),
            'DONOR OPPORTUNITIES (RAW)'[Business Unit, Regular Giving] = FALSE(),
            'DONOR OPPORTUNITIES (RAW)'[Supporter ID] <> BLANK(),
            FILTER(
                ALL('02: Calendar'),
                '02: Calendar'[Year ID] = MAX('02: Calendar'[Year ID]) - 1 &&
                '02: Calendar'[Month ID] <= MAX('02: Calendar'[Month ID])
            )
        )
    )
)

This measure creates 2 tables, for one Current FYTD, another for FYTD, with the filtering taking place within this measure. It then takes the difference to get the total New Customers.

I've done the same for Returning, using INSERSECT rather than EXPECT. To get Lost Customers I use the same measure as above, but swap around the FILTER sections.

That was a lot of work!

Mark

[Jihwan_Kim]

Hi,

Please try something like below if it suits your requirement.

Total Single Giving Donors New (FYTD) =
SUMX (
    VALUES ( 'TableName'[Supporter ID] ),
    IF (
        [Total Single Giving Donation Amount (FYTD)] > 0
            && [Total Single Giving Donation Amount (PFYTD)] <= 0,
        1,
        0
    )
)