Forum Discussion

rellis's avatar
rellis
New Member
4 years ago
Solved

Calculating Donor/Customer Downgrades

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!

 

 

Need Help

2 Replies

  • vanessafvg's avatar
    vanessafvg
    Icon for Community Champion rankCommunity Champion
    4 years ago

     

    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
        )

  • v-luwang-msft's avatar
    v-luwang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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