# Weekly Leadership Snapshot: Power BI rebuild guide

This guide covers what you need to rebuild the Weekly Leadership Snapshot as a one-page Power BI report: the tables, the Power Query steps, the relationships, the key DAX measures, and the visuals. The HTML dashboard in this kit shows the finished layout.

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

## Data files

| File (in the data folder) | What it holds |
|---|---|
| Headcounts.csv | Worship room headcounts by event time (attendance_type, total). |
| Check-Ins.csv and Check-In Event Times.csv | Children checked in to kids rooms, by location and kind (Regular, Guest, Volunteer). |
| Guest Pathway.csv | One row per first-time adult guest with the first visit date. |
| Donations.csv, Designations.csv, Funds.csv, General Fund Budget.csv | Giving by received date and the monthly budget. |
| Group Events.csv and Group Attendances.csv | Group meetings and who attended. |
| Plan People.csv and Needed Positions.csv | Serving schedules and the positions each Sunday needs. |

## Prepare the model

1. Load the CSV files with Get Data, Text/CSV, and set every column ending in _id to Text.
2. In Check-Ins, merge Check-In Event Times on event_time_id to bring in starts_at, then add a service_date column with Date.From([starts_at]).
3. In Headcounts, add service_date the same way, and add a Service column that joins campus and the start time (for example, Main Campus 9:00 AM).
4. In Group Attendances, merge Group Events on event_id to bring in starts_at and attendance_submitted, then add week_ending = Date.EndOfWeek(Date.From([starts_at]), Day.Monday), which returns the Sunday that ends each week.
5. Add the same week_ending column to Group Events.
6. Create the Date table below, mark it as the date table, and relate Date[Date] to Headcounts[service_date], Check-Ins[service_date], Guest Pathway[first_visit_date], Donations[received_at], Group Events[week_ending], Group Attendances[week_ending], Plan People[sort_date], and Needed Positions[sort_date]. Relate the General Fund Budget table to Date on month_start, and follow the Giving Analytics guide for the giving relationships.

## 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
    )
```

**Selected Sunday**

```dax
Selected Sunday = MAX ( 'Date'[Date] )
```

**Worship Headcount**

```dax
Worship Headcount =
CALCULATE (
    SUM ( Headcounts[total] ),
    Headcounts[event_name] IN { "Sunday Services", "North Fork Sunday" }
)
```

**Kids Checked In**

```dax
Kids Checked In =
CALCULATE (
    COUNTROWS ( 'Check-Ins' ),
    'Check-Ins'[kind] IN { "Regular", "Guest" },
    'Check-Ins'[event_name] IN { "Sunday Services", "North Fork Sunday" }
)
```

**Attendance**

```dax
Attendance = [Worship Headcount] + [Kids Checked In]
```

**Attendance Same Sunday Last Year**

```dax
Attendance Same Sunday Last Year =
CALCULATE ( [Attendance], DATEADD ( 'Date'[Date], -364, DAY ) )
```

**Attendance Change %**

```dax
Attendance Change % =
DIVIDE ( [Attendance] - [Attendance Same Sunday Last Year], [Attendance Same Sunday Last Year] )
```

**Attendance 4-Week Average**

```dax
Attendance 4-Week Average =
VAR d = [Selected Sunday]
RETURN
    AVERAGEX (
        FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] > d - 28 && 'Date'[Date] <= d && WEEKDAY ( 'Date'[Date], 1 ) = 1 ),
        CALCULATE ( [Attendance] )
    )
```

**Attendance 12-Month Average**

```dax
Attendance 12-Month Average =
VAR d = [Selected Sunday]
RETURN
    AVERAGEX (
        FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] > d - 364 && 'Date'[Date] <= d && WEEKDAY ( 'Date'[Date], 1 ) = 1 ),
        CALCULATE ( [Attendance] )
    )
```

**First-Time Guests**

```dax
First-Time Guests =
COUNTROWS ( 'Guest Pathway' )
    + CALCULATE ( [Kids Checked In], 'Check-Ins'[kind] = "Guest" )
```

**Group Attendance**

```dax
Group Attendance =
CALCULATE ( COUNTROWS ( 'Group Attendances' ), 'Group Attendances'[attended] = TRUE () )
```

**Groups Met**

```dax
Groups Met =
CALCULATE ( DISTINCTCOUNT ( 'Group Events'[group_id] ), 'Group Events'[canceled] = FALSE () )
```

**Groups Missing Attendance**

```dax
Groups Missing Attendance =
[Groups Met]
    - CALCULATE ( [Groups Met], 'Group Events'[attendance_submitted] = TRUE () )
```

**Positions Needed**

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

**Positions Confirmed**

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

**Waiting on Reply**

```dax
Waiting on Reply =
CALCULATE ( COUNTROWS ( 'Plan People' ), 'Plan People'[status] = "Unconfirmed" )
```

**Serving Coverage %**

```dax
Serving Coverage % =
DIVIDE ( MIN ( [Positions Confirmed], [Positions Needed] ), [Positions Needed] )
```

**General Fund YTD and Budget YTD**

```dax
General Fund YTD and Budget YTD use the same measures as the Giving Analytics guide:
General Fund YTD = CALCULATE ( [General Fund Given], DATESYTD ( 'Date'[Date] ) )
Budget YTD = CALCULATE ( [Budget], DATESYTD ( 'Date'[Date] ) )
```

## Visuals

| Visual | Fields and notes |
|---|---|
| Slicer | Date[Date] as a dropdown, filtered to Is Sunday = TRUE, single select. |
| Hero card and line chart | Card: Attendance with Attendance Change % below it. Line chart: X Date[Date] (last 26 Sundays), lines Attendance and Attendance Same Sunday Last Year. Apply a visual-level filter that keeps the 26 Sundays before the selected one. |
| Two small cards | Attendance 4-Week Average and Attendance 12-Month Average, each with the same measure shifted back 364 days for comparison. |
| Bar chart by service | Y: Headcounts[Service]. X: Attendance, with Attendance Same Sunday Last Year as a target line or error bar. |
| Kids rooms bar chart | Y: Check-Ins[location], grouped by campus. X: Kids Checked In and the same measure for last year. |
| Guests card and column chart | First-Time Guests for the selected Sunday and a 13-week column chart. |
| Giving cards | General Fund YTD, Budget YTD, and the percent of budget, plus Giving Units for the year. |
| Groups and serving | Group Attendance with Groups Missing Attendance, and a stacked bar by Plan People[team] with Positions Confirmed, Waiting on Reply, and the open remainder. |

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.