Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

DAX: SUM an IF statement comparing 2 Measures

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)...
Show and Tell
  • Anonymous's avatar
    Anonymous
    1 year ago

    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

     

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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