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.
