Calculating Donor/Customer Downgrades

[rellis]

Hi All,

Beginner here! I've just started using PBI in the last few months with success but I've gotten stuck trying to build a donor/customer upgrade, downgrade, or held report. So far I've worked out how to calculate the count of donors/customers that have upgraded or held in comparison to the same time period last fiscal year but am having trouble with downgrades. I want my measure to count those who have a negative Total Year Giving Diff YOY but it appears that it is only counting donors/customers that have at least given one gift this year (Total Giving). I'd like it to also count those that haven't given yet this year as well. Below is the DAX for the Downgrades measure and a copy of my table so far.

Any idea what I'm doing wrong? THANK YOU IN ADVANCE!

[v-luwang-msft · Accepted solution]

hI @rellis ,

Pls taste the below:

Downgrades =
IF (
    Gift[Total Giving Diff YOY] < 0,
    CALCULATE (
        DISTINCTCOUNT ( Gift[Constituent_id] ),
        FILTER ( ALL ( Gift ), Gift[Total Giving Diff YOY] < 0 )
    ),
    BLANK ()
)

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[vanessafvg]

change to insert your code

Downgrade =
VAR giving = [Difference]
RETURN
    IF (
        giving < 0,
        IF (
            ISBLANK ( SUM ( Test[TY] ) ),
            DISTINCTCOUNT ( Test[id] ),
            DISTINCTCOUNT ( Test[id] )
        ),
        0
    )