Creating a Snapshot table for larger datsets

Hi KavithaN ,

Create a Date Table: If you don't have one, create a comprehensive date table. This is crucial for time-based analysis.

 

Write DAX for the Snapshot Table:

Identify Referrals with at least two completed appointments: You'll need to count completed appointments per referral.
Filter by appointment completion date: Ensure the second completed appointment is within the month.

SnapshotTable =
VAR MonthlySnapshots =
    CALCULATETABLE (
        ADDCOLUMNS (
            VALUES ( Referrals[RefID] ),
            "SnapshotMonth", EOMONTH ( TODAY (), 0 ) // Placeholder for actual month iteration
        ),
        FILTER (
            Appointments,
            Appointments[Status] = "Completed"
        )
    )
VAR ReferralsWithTwoCompleted =
    SUMMARIZE (
        FILTER (
            Appointments,
            Appointments[Status] = "Completed"
        ),
        Appointments[RefID],
        "CompletedApptCount", COUNTROWS ( Appointments )
    )
VAR ReferralsMeetingCriteria =
    FILTER (
        ReferralsWithTwoCompleted,
        [CompletedApptCount] >= 2
    )
VAR FinalSnapshot =
    FILTER (
        ReferralsMeetingCriteria,
        VAR CurrentRefID = ReferralsMeetingCriteria[RefID]
        VAR MaxDateForRef =
            CALCULATE (
                MAX ( Appointments[AppointmentDate] ),
                Appointments[RefID] = CurrentRefID,
                Appointments[Status] = "Completed"
            )
        RETURN
            MaxDateForRef <= EOMONTH ( TODAY (), 0 ) // Placeholder for month iteration
    )
RETURN
    SELECTCOLUMNS (
        FinalSnapshot,
        "RefID", [RefID],
        "SnapshotMonth", [SnapshotMonth] // This needs to be dynamic for each month's snapshot
    )

 

Refinement for Monthly Snapshots: To create a true monthly snapshot, you'll need to iterate over months. A common pattern is to use EOMONTH and CALENDAR functions.

MonthlyReferralSnapshot =
VAR MinDate = MIN(Appointments[AppointmentDate])
VAR MaxDate = MAX(Appointments[AppointmentDate])
VAR DateRange = CALENDAR(MinDate, MaxDate) // Or a fixed date range relevant to your data
VAR Snapshots =
    ADDCOLUMNS(
        DateRange,
        "ReferralsWithTwoCompletedByMonthEnd",
        VAR CurrentMonthEndDate = [Date]
        VAR ReferralsInMonth =
            CALCULATETABLE (
                VALUES ( Appointments[RefID] ),
                FILTER (
                    Appointments,
                    Appointments[Status] = "Completed" &&
                    Appointments[AppointmentDate] <= CurrentMonthEndDate
                )
            )
        VAR CountCompletedAppsPerReferral =
            SUMMARIZE (
                FILTER (
                    Appointments,
                    Appointments[Status] = "Completed" &&
                    Appointments[AppointmentDate] <= CurrentMonthEndDate
                ),
                Appointments[RefID],
                "ApptCount", COUNTROWS ( Appointments )
            )
        VAR ReferralsMeetingCriteria =
            FILTER (
                CountCompletedAppsPerReferral,
                [ApptCount] >= 2
            )
        VAR FinalReferralIDs =
            SELECTCOLUMNS ( ReferralsMeetingCriteria, "RefID", [RefID] )
        VAR ValidReferrals =
            INTERSECT (
                VALUES ( Referrals[RefID] ),
                FinalReferralIDs
            )
        RETURN
            COUNTROWS ( ValidReferrals ) // This returns a count for the month, not the list of RefIDs
    )
RETURN
    -- This DAX above is illustrative. A better approach for a snapshot table
    -- would involve creating a table that lists the RefIDs for each month.

-- Corrected DAX for a snapshot table structure:
MonthlyReferralSnapshotTable =
VAR RefAppts =
    CALCULATETABLE (
        Appointments,
        Appointments[Status] = "Completed"
    )
VAR RefApptCounts =
    SUMMARIZE (
        RefAppts,
        Appointments[RefID],
        "TotalCompletedAppointments", COUNTROWS ( RefAppts )
    )
VAR ReferralsWithSufficientAppointments =
    FILTER (
        RefApptCounts,
        [TotalCompletedAppointments] >= 2
    )
VAR AllMonths =
    SELECTCOLUMNS (
        CALENDAR ( DATE ( YEAR ( MIN ( Appointments[AppointmentDate] ) ), MONTH ( MIN ( Appointments[AppointmentDate] ) ), 1 ), DATE ( YEAR ( MAX ( Appointments[AppointmentDate] ) ), MONTH ( MAX ( Appointments[AppointmentDate] ) ), 1 ) ),
        "MonthStartDate", [Date]
    )
VAR SnapshotResult =
    GENERATEALL(
        AllMonths,
        VAR CurrentMonthStartDate = AllMonths[MonthStartDate]
        VAR CurrentMonthEndDate = EOMONTH(CurrentMonthStartDate, 0)
        VAR ReferralsForThisMonth =
            FILTER(
                ReferralsWithSufficientAppointments,
                VAR RefID = ReferralsWithSufficientAppointments[RefID]
                VAR LastTwoApptDates =
                    TOPN(
                        2,
                        CALCULATETABLE(
                            Appointments,
                            Appointments[RefID] = RefID,
                            Appointments[Status] = "Completed",
                            Appointments[AppointmentDate] <= CurrentMonthEndDate
                        ),
                        Appointments[AppointmentDate], DESC
                    )
                RETURN
                    COUNTROWS(LastTwoApptDates) = 2 && MAX(LastTwoApptDates[AppointmentDate]) <= CurrentMonthEndDate
            )
        RETURN
            SELECTCOLUMNS(
                ReferralsForThisMonth,
                "RefID", [RefID],
                "SnapshotMonth", CurrentMonthStartDate // Store the start of the month
            )
    )
RETURN
    SnapshotResult

 

Consideration: This DAX can be resource-intensive for very large datasets.

 

Please mark this post as solution if it helps you. Appreciate Kudos.