The Compliance Calendar on Microsoft 365
Every check in the standard, mapped to the list, flow or measure that enforces it. Written to be executed rather than read, including by an agent working in your tenant.
Used by
- AI Officer
- Business Systems Analyst
- Operations Manager
- Compliance Officer
How to get this live in your business
Free. The Compliance Calendar Build Standard: the objects, the checks in order, and the pass test.
Ready for
The standard says what to build and deliberately says nothing about where. This says where, for the one stack most Australian SMBs already pay for. It is written so that somebody, or something, with access to your tenant can work through it in order and end up with a calendar that passes the standard’s own pass test.
In short
What this is
A build plan for one standard on one stack: three lists, one library, four flows, six measures, in the order they have to be built.
The core rule
The derive step is the whole standard. If a person can type a due date and nothing notices, the rest of this is decoration.
What it does not do
No legal notice leaves on a timer. The escalation flow drafts and asks; a person releases it.
The hard part
Not the logic. The write-back loop, which is the reason most attempts at this are abandoned in the first week.
Take the spec to Claude, ChatGPT or Copilot. The sentence to paste is one click away.
Want the whole thing by email?
This paper, plus the rest of the library. One click to stop them.
1. Before you start
Read the compliance calendar build standard first. It is the specification and this page is one way to satisfy it. Where the two disagree, the standard wins and this page has a bug. The shared conventions for this stack, and the reasons behind them, are in building the standards on Microsoft 365.
What you need in the tenant: a SharePoint site you can create lists in, Power Automate with the standard SharePoint and Approvals connectors (no premium connector is required for this build), and a Power BI workspace with somewhere to publish. Nothing here needs Dataverse.
Three things an agent cannot do for you, so decide them first: who owns the site, which security group holds the people allowed to approve a notice, and whether the Power BI workspace already exists. Everything after that is executable.
2. The lists, with their columns
Three lists and one library. Internal names matter: a flow and a Power BI model both address columns by internal name, and renaming a column later does not change it.
For your developer. Show the fields
Site: Compliance
LIST Rules (append only; supersede, never edit)
RuleId Text indexed, REQUIRED
Jurisdiction Choice the states you operate in
the states you operate in
Requirement Text
IntervalMonths Number integer
AnchorBasis Choice last_completed | installed | commissioned
EffectiveFrom DateTime REQUIRED
EffectiveTo DateTime blank means current
Citation Text what says so, so a person can check
RuleVersion Text REQUIRED
LIST Obligations
Title Text "<PropertyRef> - <RuleId>"
PropertyRef Text indexed
Rule Text lookup to Rules
RuleVersion Text snapshot of the version that derived DueDate
AnchorDate DateTime
AnchorSource Choice known | unknown | estimated
DueDate DateTime indexed. WRITTEN BY FLOW 1 ONLY. Not on any form.
Responsible Choice agency | owner | tenant
State Choice indexed. not_due | due | done | missed
LastCompleted DateTime
DueOverride DateTime a person may write this, and it is measured
OverrideBy Person
OverrideReason Text
LateCompletion Boolean done after missed. Never clears the history.
LIST StateChanges (append only, enforced by permissions)
Obligation Text indexed. lookup to Obligations
FromState Text
ToState Text
At DateTime
By Text a person, or the rule that fired
Reason Text
LIBRARY Evidence
Obligation Text indexed. lookup to Obligations
CapturedOn DateTime
CapturedBy Person
Index DueDate, State and PropertyRef before the list grows.DueDate is a plain Date column rather than a calculated one on purpose, and the reason is in the traps below: a calculated column cannot reach a lookup, which is where the interval lives.
3. Every check, and what enforces it
This is the join the standard leaves to you. Each row is a line from its conformance checklist and the thing in the tenant that makes it true.
| The check | Enforced by | How |
|---|---|---|
| Rules are versioned and never edited in place | List design | Rules is append-only. Superseding a rule writes a new item and stamps EffectiveTo on the old one. No flow needed, but the permission level is: a rule editor cannot delete. |
| Due dates are derived, never stored as a typed fact | Flow 1 | DueDate is written only by the derive flow. The column is not on any edit form. A person who must override writes DueOverride instead, and it is measured. |
| A rule change recomputes future obligations without rewriting history | Flow 2 | Changing Rules fans out to every obligation on that rule whose anchor precedes the new EffectiveFrom. StateChanges is untouched, so what happened still reads as what happened. |
| An unknown anchor is an explicit state that surfaces | Flow 1 + a measure | AnchorSource = unknown short-circuits the derive: DueDate stays blank and the row appears in the Unknown Anchors measure. It never defaults to today. |
| Evidence of completion, not a completion flag | Flow 3 | The transition to done is refused unless an Evidence item exists for the obligation. The refusal is the gate; a reminder is not. |
| A refusal is a dated outcome and the obligation stays live | Flow 3 | Responsible party declining is a StateChanges row with a reason. State does not move to done and DueDate does not move. |
| Nothing sends a notice without a person releasing it | Flow 4 | The escalation flow drafts and posts for approval. The send action sits after an approval response, never on the timer itself. |
| A breached obligation never clears itself | Flow 3 + Flow 4 | missed is terminal for that cycle. Completing late writes done with a LateCompletion flag and the StateChanges row keeps the missed transition. |
4. The four flows
Written as trigger, guard, logic. Deliberately not as click paths: Microsoft renames the UI and these do not change.
For your developer. Show the fields
FLOW 1 Derive the due date
trigger Obligations: item created or modified
guard skip if the last editor is this flow's own identity
(otherwise it triggers itself, forever)
logic if AnchorSource = unknown -> clear DueDate, stop
rule = the Rules item where RuleId matches and
EffectiveFrom <= AnchorDate < EffectiveTo
anchor = LastCompleted or AnchorDate, per rule.AnchorBasis
DueDate = addToTime(anchor, rule.IntervalMonths, 'Month')
write DueDate and RuleVersion
FLOW 2 Rule change fan-out
trigger Rules: item created or modified
logic find Obligations where Rule = this rule
and State in (not_due, due)
touch each one so FLOW 1 re-derives it
never do not touch StateChanges. History stays as it was.
FLOW 3 State transition gate
trigger an HTTP or button request, never a manual column edit
logic to done -> refuse unless an Evidence item exists
for this obligation
to done
from missed -> also set LateCompletion = yes
refusal by the responsible party
-> append StateChanges with the reason,
State unchanged, DueDate unchanged
always -> append a StateChanges row
note State is not on the edit form. This flow is its only writer.
FLOW 4 Escalation, and the notice a person releases
trigger scheduled, daily
logic DueDate < today and State = due -> State = missed (via FLOW 3)
DueDate within the lead time -> State = due (via FLOW 3)
draft the notice, start an approval
send ONLY after an approved response
never send on the timer. The approval is the gate.Flow 3 exists so that State has exactly one writer. Letting people set a choice column directly is what turns an audit trail into a column somebody edited last Tuesday.
5. The Power BI model and its measures
One model over the three lists. Relationships: Rules 1 to many Obligations, Obligations 1 to many StateChanges and Evidence. Measures belong in the model, not in a report filter where only their author can find them.
For your developer. Show the fields
Derived Rate =
VAR Total = COUNTROWS ( Obligations )
RETURN DIVIDE (
CALCULATE ( COUNTROWS ( Obligations ), ISBLANK ( Obligations[DueOverride] ) ),
Total
)
Override Rate = 1 - [Derived Rate]
Coverage =
DIVIDE (
CALCULATE (
COUNTROWS ( Obligations ),
NOT ISBLANK ( Obligations[DueDate] ),
Obligations[State] IN { "not_due", "due" }
),
CALCULATE ( COUNTROWS ( Obligations ), Obligations[State] <> "done" )
)
Unknown Anchors =
CALCULATE ( COUNTROWS ( Obligations ), Obligations[AnchorSource] = "unknown" )
Missed Open =
CALCULATE ( COUNTROWS ( Obligations ), Obligations[State] = "missed" )
Breach Ageing Days =
AVERAGEX (
FILTER ( Obligations, Obligations[State] = "missed" ),
DATEDIFF ( Obligations[DueDate], TODAY (), DAY )
)
Late Completions =
CALCULATE ( COUNTROWS ( Obligations ), Obligations[LateCompletion] = TRUE )
-- Row level security, if more than one office or region reads this:
-- role "Territory", table Obligations,
-- [PropertyRef] IN
-- SELECTCOLUMNS (
-- FILTER ( Territories, Territories[Upn] = USERPRINCIPALNAME () ),
-- "p", Territories[PropertyRef]
-- )Override Rate is the one to put on the front page. It is the measure that says whether the derive step is being worked around, and a rising line there means the calendar is drifting back to a spreadsheet with extra steps.
6. The order to build it in
The order matters because each step is testable only once the previous one exists. An agent working through this should stop and verify at each numbered point rather than building everything and testing at the end.
- Create the site and the three lists with the columns above. Index DueDate, State and PropertyRef now.
- Break inheritance on StateChanges and apply an add-only permission level. Verify by trying to edit a row as a normal user.
- Load Rules for one jurisdiction. Two versions of one rule, so the effective-date logic has something to choose between.
- Build Flow 1. Test with one obligation, then with an obligation whose AnchorSource is unknown, which must end with a blank DueDate rather than today.
- Remove DueDate from the edit form and confirm a person cannot type one. This is the step people skip, and it is the standard.
- Build Flow 2. Supersede a rule and confirm future dates move while StateChanges is untouched.
- Build Flow 3, then Flow 4. Confirm no notice can leave without an approval response.
- Connect Power BI, add the measures, publish. Add row level security only if more than one group reads it.
- Run the pass test below. It is the standard’s own, not a new one.
7. Five traps specific to this build
These are not general advice. Each one breaks this particular calendar on this particular stack, and the first one accounts for most abandoned attempts.
The derive flow re-triggers itself
A flow on "item created or modified" that writes back to the same list fires itself again, forever. Add a trigger condition that skips items whose last editor is the flow's own identity, or write through a second column the trigger ignores. This is the single most common reason this build is abandoned in week one.
Reaching for a calculated column for DueDate
SharePoint calculated columns cannot reference a Lookup or Person column, and the interval lives in Rules, which is a lookup. It also would not recompute when the rule changed, only when the row did. Calculated columns are the obvious answer here and they cannot do this job.
The 5,000 item list view threshold
An obligation per property per rule reaches five figures quickly. Index DueDate and State before the list grows, not after: indexing a list that is already over the threshold is materially harder, and every view and flow query that filters on an unindexed column starts failing at once.
StateChanges that anyone can edit
An append-only history that can be edited is not a history. Break inheritance on that list and grant a custom permission level with Add Items and without Edit Items or Delete Items. SharePoint has no append-only list type, so this has to be built deliberately.
Power BI pointed at the list with the default connector settings
Use the SharePoint Online list connector on its 2.0 implementation and select columns explicitly. The default pulls every system field including several that break refresh on a schema change, and a model built on the wide table is slow enough that people stop opening it.
8. Where the automation stops
An agent with access to the tenant can create the lists, set the columns, build the flows and write the measures. Being honest about the rest matters more than the demo:
- Creating the site, and any tenant-level consent, needs somebody with the administrative role. Nothing here should be built by granting an agent that role.
- Publishing a Power BI model and assigning workspace access is a licensed human action, at least the first time.
- The rule set is yours. Loading the correct instruments for your jurisdiction is a professional judgement and it is where a wrong build looks right.
- Approving a notice is a person by design, and that is the one thing in this plan that must never be automated away.
The pass test. The standard’s test, run against the tenant. Change one rule’s interval and give the change an effective date. Every obligation derived from that rule must show a new DueDate, and each must still carry the RuleVersion that produced the old one. Then take an obligation in the missed state, complete it, and confirm the StateChanges history still contains the missed transition and LateCompletion is set. Both take a minute against your own data. If the first needs anyone to retype a date, Flow 1 is not the only writer of DueDate; if the second reads as though it was never missed, you have a status column rather than a history.