Forum Discussion
DAX: SUM an IF statement comparing 2 Measures
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
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
Super UserHi,
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.
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
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
- I have 2 tables