I want to create a line chart that summarizes a percentage by month. The table has a column of type Date like 7/11/24 and a column of 0/1 to compute the percentage of 1s out of the total.

Example:

My thought was to create a new column for month, then a new table that aggregates (count of 1s) / total groupby month, then use that for the line table. Is there any easier way? And should I use STARTOFMONTH instead of the auto-generated table[dischargedate].[Month] because that only specifies month without year?

Here’s what I have so far, but it isn’t particularly elegant especially compared to the equivalent SQL:

New column

Discharge Month = STARTOFMONTH(index[dischargedate])

New table

Table = SUMMARIZE(index, index[Discharge Month], 
    "Successful Followup %", CALCULATE(COUNTROWS(index), index[followup]="Yes") / COUNTROWS(index))

SQL-like idea (not tested):

SELECT SUM(CASE WHEN followup = 'yes' THEN 1 ELSE 0 END) / COUNT(*)
FROM index
GROUP BY MONTH(dischargedate), YEAR(dischargedate)