# Guest to Member Pathway: Power BI rebuild guide

This guide covers what you need to rebuild the Guest to Member Pathway report in Power BI. The pathway is built from one prepared table with a row for each first-time adult guest, plus the workflow card tables for the stall-point section.

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

## Data files

| File (in the data folder) | What it holds |
|---|---|
| Guest Pathway.csv | One row per first-time adult guest: first_visit_date, campus, followup_call_date, discover_class_date, first_group_date, first_served_date, member_since. |
| Workflow Cards.csv | The First-Time Guest Follow-Up workflow: current_step, stage, assignee, moved_to_step_at, overdue. |
| Workflow Card Steps.csv | Each completed step with its date and who completed it. |

## Prepare the model

1. The Guest Pathway table is prepared from Planning Center exports. The first visit is the Sunday on or just before the connect card was entered (the workflow card's created date), the call date from the completed Pastor phone call step, the class date from the Discover class event in Check-Ins, the group date from the earliest Groups membership joined date, the serving date from the earliest confirmed Services plan, and member_since from the membership date field in People. In a live setup these merges happen in Power Query, and the file in this kit is the result.
2. Load the three CSV files, set ID columns to Text, and set every date column to Date (or Date/Time on the workflow tables).
3. Create the Date table and relate it to Guest Pathway[first_visit_date].
4. Add the calculated columns below to Guest Pathway.

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

**Guest Pathway[Guest Year]**

```dax
Guest Year =
VAR y = IF ( MONTH ( 'Guest Pathway'[first_visit_date] ) >= 10, YEAR ( 'Guest Pathway'[first_visit_date] ), YEAR ( 'Guest Pathway'[first_visit_date] ) - 1 )
RETURN "Oct " & y & " to Sep " & ( y + 1 )
```

**Guest Pathway[Furthest Step]**

```dax
Furthest Step =
SWITCH (
    TRUE (),
    NOT ISBLANK ( 'Guest Pathway'[member_since] ), "6. Became a member",
    NOT ISBLANK ( 'Guest Pathway'[first_served_date] ), "5. Started serving",
    NOT ISBLANK ( 'Guest Pathway'[first_group_date] ), "4. Joined a group",
    NOT ISBLANK ( 'Guest Pathway'[discover_class_date] ), "3. Attended Discover class",
    NOT ISBLANK ( 'Guest Pathway'[followup_call_date] ), "2. Follow-up call made",
    "1. First visit"
)
```

**Guest Pathway[Days to Call]**

```dax
Days to Call =
IF ( ISBLANK ( 'Guest Pathway'[followup_call_date] ), BLANK (), DATEDIFF ( 'Guest Pathway'[first_visit_date], 'Guest Pathway'[followup_call_date], DAY ) )
```

**First-Time Guests**

```dax
First-Time Guests = COUNTROWS ( 'Guest Pathway' )
```

**Called**

```dax
Called =
CALCULATE ( [First-Time Guests], NOT ISBLANK ( 'Guest Pathway'[followup_call_date] ) )
```

**Attended Class**

```dax
Attended Class =
CALCULATE ( [First-Time Guests], NOT ISBLANK ( 'Guest Pathway'[discover_class_date] ) )
```

**Joined Group**

```dax
Joined Group =
CALCULATE ( [First-Time Guests], NOT ISBLANK ( 'Guest Pathway'[first_group_date] ) )
```

**Started Serving**

```dax
Started Serving =
CALCULATE ( [First-Time Guests], NOT ISBLANK ( 'Guest Pathway'[first_served_date] ) )
```

**Became Member**

```dax
Became Member =
CALCULATE ( [First-Time Guests], NOT ISBLANK ( 'Guest Pathway'[member_since] ) )
```

**Share of Guests (example)**

```dax
Joined Group % = DIVIDE ( [Joined Group], [First-Time Guests] )
```

**Median Days to Call**

```dax
Median Days to Call = MEDIAN ( 'Guest Pathway'[Days to Call] )
```

**Median Days to Group**

```dax
Median Days to Group =
MEDIANX (
    FILTER ( 'Guest Pathway', NOT ISBLANK ( 'Guest Pathway'[first_group_date] ) ),
    DATEDIFF ( 'Guest Pathway'[first_visit_date], 'Guest Pathway'[first_group_date], DAY )
)
```

**75th Percentile Days to Group**

```dax
P75 Days to Group =
PERCENTILEX.INC (
    FILTER ( 'Guest Pathway', NOT ISBLANK ( 'Guest Pathway'[first_group_date] ) ),
    DATEDIFF ( 'Guest Pathway'[first_visit_date], 'Guest Pathway'[first_group_date], DAY ),
    0.75
)
```

**Report Date**

```dax
Report Date = DATE ( 2026, 10, 11 )    // use TODAY () in a live report
```

**Open Cards**

```dax
Open Cards =
CALCULATE ( COUNTROWS ( 'Workflow Cards' ), 'Workflow Cards'[stage] IN { "ready", "snoozed" } )
```

**Past Due Cards**

```dax
Past Due Cards =
CALCULATE ( [Open Cards], 'Workflow Cards'[overdue] = TRUE () )
```

**Median Wait Days**

```dax
Median Wait Days =
MEDIANX (
    FILTER ( 'Workflow Cards', 'Workflow Cards'[stage] IN { "ready", "snoozed" } ),
    DATEDIFF ( 'Workflow Cards'[moved_to_step_at], [Report Date], DAY )
)
```

## Visuals

| Visual | Fields and notes |
|---|---|
| Slicer | Guest Pathway[Guest Year] as buttons, single select. |
| Pathway columns | Six cards or a column chart: First-Time Guests, Called, Attended Class, Joined Group, Started Serving, Became Member, each with its share of First-Time Guests. |
| Where people stop | Column chart: X Guest Pathway[Furthest Step], Y First-Time Guests. |
| Time between steps | Table or bar chart of the median and 75th percentile measures for each pair of steps (repeat the Median Days to Group pattern for the other pairs). |
| Days to the phone call | Line chart: X Date[Month Start], Y Median Days to Call, with a constant line at 7. |
| Open cards by step | Table: Workflow Cards[current_step], Open Cards, Past Due Cards, Median Wait Days, and the assignee. |
| Campus comparison | Matrix with Guest Pathway[campus] on rows and the counts and Median Days to Call as values. |

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.