# Volunteer Health: Power BI rebuild guide

This guide covers what you need to rebuild the Volunteer Health Report in Power BI from Planning Center Services data: response rates by team, people who may need a break, coverage for coming Sundays, and new volunteers.

_Sample data. All names, organizations, and figures are fictional._

## Data files

| File (in the data folder) | What it holds |
|---|---|
| Plan People.csv | Each scheduling request: plan date, team, position, person, status (Confirmed, Unconfirmed, Declined), and decline reason. |
| Needed Positions.csv | How many people each plan needs for each team and position. |
| Service Plans.csv | Plan dates and service types for each campus. |

## Prepare the model

1. Load the three CSV files, set ID columns to Text, and set sort_date to Date.
2. Create the Date table and relate it to Plan People[sort_date] and Needed Positions[sort_date].
3. Add a Quarter column to the Date table ("Q" & QUARTER([Date]) & " " & YEAR([Date])) for the quarter slicer.
4. Set the report date measure to the last Sunday with confirmed serving, or to TODAY () in a live report.

## Key DAX

**Date (calculated table)**

```dax
Date =
VAR firstDay = DATE ( 2023, 1, 1 )
VAR lastDay = DATE ( 2026, 12, 31 )
RETURN
    ADDCOLUMNS (
        CALENDAR ( firstDay, lastDay ),
        "Year", YEAR ( [Date] ),
        "Month Start", DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 1 ),
        "Is Sunday", WEEKDAY ( [Date], 1 ) = 1
    )
```

**Report Sunday**

```dax
Report Sunday = DATE ( 2026, 10, 11 )    // use the most recent Sunday in a live report
```

**Requests**

```dax
Requests = COUNTROWS ( 'Plan People' )
```

**Accepted %**

```dax
Accepted % =
DIVIDE ( CALCULATE ( [Requests], 'Plan People'[status] = "Confirmed" ), [Requests] )
```

**Declined %**

```dax
Declined % =
DIVIDE ( CALCULATE ( [Requests], 'Plan People'[status] = "Declined" ), [Requests] )
```

**No Reply %**

```dax
No Reply % =
DIVIDE ( CALCULATE ( [Requests], 'Plan People'[status] = "Unconfirmed" ), [Requests] )
```

**Accepted % Last Year**

```dax
Accepted % Last Year =
CALCULATE ( [Accepted %], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
```

**Volunteers Serving**

```dax
Volunteers Serving =
CALCULATE ( DISTINCTCOUNT ( 'Plan People'[person_id] ), 'Plan People'[status] = "Confirmed" )
```

**Positions Needed**

```dax
Positions Needed = SUM ( 'Needed Positions'[quantity] )
```

**Positions Confirmed**

```dax
Positions Confirmed =
CALCULATE ( [Requests], 'Plan People'[status] = "Confirmed" )
```

**Open Positions**

```dax
Open Positions =
MAX (
    0,
    [Positions Needed] - [Positions Confirmed]
        - CALCULATE ( [Requests], 'Plan People'[status] = "Unconfirmed" )
)
```

**Sundays Served Last 13**

```dax
Sundays Served Last 13 =
VAR d = [Report Sunday]
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Plan People'[sort_date] ),
        'Plan People'[status] = "Confirmed",
        REMOVEFILTERS ( 'Date' ),
        'Date'[Date] > d - 91,
        'Date'[Date] <= d
    )
```

**Current Streak**

```dax
Current Streak =
VAR d = [Report Sunday]
VAR sundays =
    FILTER ( ALL ( 'Date'[Date] ), WEEKDAY ( 'Date'[Date], 1 ) = 1 && 'Date'[Date] <= d && 'Date'[Date] > d - 7 * 60 )
VAR served =
    CALCULATETABLE (
        VALUES ( 'Plan People'[sort_date] ),
        'Plan People'[status] = "Confirmed",
        REMOVEFILTERS ( 'Date' )
    )
VAR missed = EXCEPT ( sundays, served )
VAR lastMissed = MAXX ( missed, 'Date'[Date] )
RETURN IF ( ISBLANK ( lastMissed ), 60, INT ( ( d - lastMissed ) / 7 ) )
```

**Needs a Break**

```dax
Needs a Break =
IF ( [Sundays Served Last 13] >= 10 || [Current Streak] >= 6, 1 )
```

**New Volunteers**

```dax
New Volunteers =
VAR periodStart = MIN ( 'Date'[Date] )
VAR periodEnd = MAX ( 'Date'[Date] )
VAR people =
    CALCULATETABLE ( VALUES ( 'Plan People'[person_id] ), 'Plan People'[status] = "Confirmed" )
RETURN
    COUNTROWS (
        FILTER (
            people,
            VAR firstServed =
                CALCULATE (
                    MIN ( 'Plan People'[sort_date] ),
                    'Plan People'[status] = "Confirmed",
                    REMOVEFILTERS ( 'Date' )
                )
            RETURN firstServed >= periodStart && firstServed <= periodEnd
        )
    )
```

## Visuals

| Visual | Fields and notes |
|---|---|
| Slicer | Date[Quarter], single select. The coverage visuals use their own filter for the next four Sundays after Report Sunday. |
| Cards | Volunteers Serving, Accepted %, Declined %, people with Needs a Break, Open Positions for the next four Sundays, and New Volunteers. |
| Response by team | 100% stacked bar: Y Plan People[team], values Accepted %, Declined %, No Reply %. Add Accepted % Last Year as a tooltip or reference marker. |
| Needs a break table | Table: person name, team, Sundays Served Last 13, Current Streak, filtered to Needs a Break = 1 and sorted by Current Streak. |
| Coverage for coming Sundays | Stacked bar: Y Date[Date] for the next four Sundays, values Positions Confirmed, Unconfirmed requests, and Open Positions. A table below lists the positions with the most open spots. |
| New volunteers | Table of people where New Volunteers = 1 for the quarter, and a column chart of New Volunteers by Date[Quarter]. |
| Decline reasons | Bar chart: Plan People[decline_reason] with Requests, filtered to Declined. |

Apply the theme file from the Giving Analytics kit (Wren Hollow Giving Theme.json) or a church's own colors. Check the finished numbers against the HTML dashboard before sharing the report.