Power Bi: dax formula

Veronica Krivis

I need to create a Dax formula to count the donors who gave in 2024 but not in 2023 and not 2022. Can you help me?

Alex Wong

@Veronica Krivis
using chatgpt.. modify your prompt a bit:

I need to create a Dax formula to count the donors who gave a gift in 2024 but not in 2023 and not 2022. assume a table of individual gift with columns: Frid, Year, Paid_v where Frid is individual donor id Year is year of gift Paid_v is amount given

Response:

You can create a DAX formula that counts donors who gave a gift in 2024 but not in 2023 or 2022 by using a combination of CALCULATE, FILTER, EXCEPT, and DISTINCT. Here's how you can structure the formula:

Donors_2024_Not_2023_2022 =
VAR Donors2024 =
CALCULATETABLE(
DISTINCT(IndividualGift[Frid]),
IndividualGift[Year] = 2024,
IndividualGift[Paid_v] > 0
)
VAR Donors2023 =
CALCULATETABLE(
DISTINCT(IndividualGift[Frid]),
IndividualGift[Year] = 2023,
IndividualGift[Paid_v] > 0
)
VAR Donors2022 =
CALCULATETABLE(
DISTINCT(IndividualGift[Frid]),
IndividualGift[Year] = 2022,
IndividualGift[Paid_v] > 0
)
RETURN
COUNTROWS(
EXCEPT(
Donors2024,
UNION(Donors2023, Donors2022)
)
)

Explanation:

1. Donors2024: This variable retrieves a distinct list of donors (Frid) who gave a gift in 2024.
2. Donors2023: This variable retrieves a distinct list of donors who gave a gift in 2023.
3. Donors2022: This variable retrieves a distinct list of donors who gave a gift in 2022.
4. EXCEPT: This function is used to exclude donors from 2024 who also gave in 2023 or 2022.
5. COUNTROWS: Finally, the formula counts the number of rows, representing the number of unique donors who donated in 2024 but not in 2023 or 2022.

This formula will give you the count of donors who donated only in 2024 and not in the previous two years.

in Power BI DAX, there is always more than one way to do a measure, try this one and see how you like it

Veronica Krivis

@Alex Wong
Thank you!! Have a nice day!!