Power BI Data (LYBUNTs, SYBUNTs, New Donors..)

Hannah White · January 21

Is there anyone who has tried to extract information such as LYBUNTs, SYBUNTs, new donors, recovered donors, etc., using a Power BI report? I want dynamic data on a dashboard that includes information like that, but I am not sure how to get that to work. Any articles or discussion posts would be helpful for me to review.

Crystal Bruce · January 22

@Hannah White tagging in All-Stars @Dan Snyder and @Alex Wong to see if they have any resources that can help!

Dan Snyder · January 23

@Hannah White This should be something you can do as long as you are able to get the historical giving information connected to your constituents. Whether you add that directly to the database as something like an attribute/custom field or what is likely a better option would be to get the data you need and then as part of the data transformations in Power BI you can label those LYBUNTS/SYBUNTS to be able to report on them. @Alex Wong may have a more specific example to share.

EDIT: One more link to add -

Alex Wong · January 23

@Hannah White
you can try some of these template and see if they meet your need (I don't remember what they all have).

https://community.blackbaud.com/forums/viewtopic/586/64602
https://community.blackbaud.com/forums/viewtopic/586/63989
https://community.blackbaud.com/forums/viewtopic/426/69945

if you want to learn more about re nxt and power bi and excel in it, @Erik Leaver does host regional workshop specifically for this, which you can check to see if there's one upcoming in your area.

Alex Wong · January 23

@Hannah White
https://community.blackbaud.com/forums/viewtopic/586/70826

This template allows you to run Query API (running a query from RE and get its result) and pass that result into Power BI directly for visualization.

This method will allow you to do what you want without needing extensive gift history record loaded into Power BI by utilizing the calcuation in Constituent Summary of Gift field.

d8983bbb1e4c241769a911a7c862a99a-huge-im — RE Query in webview Criteria of contribution giving in 2023 or 2024

3e0443f95099d4b0cee74ec72c32f439-huge-im — output of IDs, name, 2023 and 2024 contribution giving

ed2edd94b7fecea2d2861688e68c4425-huge-im — Using gift soft credit by grid amount (this depends on your org recording of soft credit)

b0fa27c427d1f32ba53a98de7e632c40-huge-im — this will give you the query JSON to run adhoc query used in template

{
"advanced_processing_options": {},
"category_id": 173,
"constituent_filters": {
"include_deceased": true,
"include_inactive": true,
"include_no_valid_addresses": true
},
"filter_fields": [
{
"compare_type": "None",
"filter_values": [
"$0.00"
],
"operator": "GreaterThan",
"query_field_id": 71937,
"summary_instance": 1
},
{
"compare_type": "Or",
"filter_values": [
"$0.00"
],
"operator": "GreaterThan",
"query_field_id": 71937,
"summary_instance": 2
}
],
"summary_fields": [
{
"summary_instance": 1,
"query_field_id": 71937,
"filter_fields": [
{
"compare_type": "None",
"filter_values": [
"1/1/2024",
"12/31/2024"
],
"operator": "Between",
"query_field_id": 536
},
{
"compare_type": "And",
"filter_values": ["1","8","9","10","18"],
"operator": "OneOf",
"query_field_id": 639
}
]
},
{
"summary_instance": 2,
"query_field_id": 71937,
"filter_fields": [
{
"compare_type": "None",
"filter_values": [
"1/1/2023",
"12/31/2023"
],
"operator": "Between",
"query_field_id": 536
},
{
"compare_type": "And",
"filter_values": ["1","8","9","10","18"],
"operator": "OneOf",
"query_field_id": 639
}
]
}
],
"format": "Dynamic",
"gift_processing_options": {
"matching_gift_credit_option": "MatchingGiftCompany",
"soft_credit_option": "Recipients",
"soft_credit_sub_option": "UseAmountInGrid"
},
"others_can_execute": true,
"others_can_modify": true,
"select_fields": [
{
"query_field_id": 349
},
{
"query_field_id": 425
},
{
"query_field_id": 597
},
{
"query_field_id": 71937,
"user_alias": "2024 Contribution",
"summary_instance": 1
},
{
"query_field_id": 71937,
"user_alias": "2023 Contribution",
"summary_instance": 2
}
],
"sort_fields": [],
"type_id": 18
}