How to approach a monthly summary from a data start that has date start and end columns

sgt_pepper 

Here's how I'd approach it:

1. Ingest the data

You already have:

• WebsiteName

• created_at

• ended_at

• duration

• status (up/down)

  Create a full website + month matrix

  Because: if there's no downtime, your data has no record — but you still want to show 100% uptime.

  Steps:

  • Create a Dates Table with all months you care about.

  • Cross-join it with your distinct WebsiteName list.

Result: even "no downtime" months will exist.

Handle downtime periods

Now, filter your original table to only status = "down" periods.

From this, for each Website + Month:

• Sum the duration (downtime in milliseconds).
  
  

  Join it together

  • Merge (left join) your Website-Month master list with the downtime sums.

If no downtime, downtime = 0.

Calculate % uptime

Simple formula:
Uptime % = (Milliseconds in month - Downtime) / Milliseconds in month
Dates Table
let
StartDate = #date(2022, 1, 1),
EndDate = Date.EndOfMonth(DateTime.Date(DateTime.LocalNow())),
Dates = List.Dates(StartDate, Duration.Days(EndDate - StartDate) + 1, #duration(1,0,0,0)),
TableDates = Table.FromList(Dates, Splitter.SplitByNothing(), {"Date"}),
AddYearMonth = Table.AddColumn(TableDates, "YearMonth", each Date.ToText([Date], "MMM-yy"), type text),
RemoveDay = Table.Distinct(Table.SelectColumns(AddYearMonth, {"YearMonth"}))
in
RemoveDay

Just pull distinct WebsiteName from your fact table.
Cross-join (all combinations):
CrossJoin = Table.AddColumn(WebsiteList, "Custom", each RemoveDay),
Expand = Table.ExpandTableColumn(CrossJoin, "Custom", {"YearMonth"})

Downtime Aggregation

• Filter status = "down".

• Create a YearMonth column based on created_at.

Group by WebsiteName, YearMonth, SUM of duration.

Merge downtime into cross-join

• Left-join the downtime sums onto your Website-Month table.

If downtime is null, replace it with 0.

Add Days in Month

Add a column:
DaysInMonth = Date.DaysInMonth(Date.FromText("01-" & [YearMonth]))
Milliseconds in a month:
MillisecondsInMonth = [DaysInMonth] * 24 * 60 * 60 * 1000

Final Calculation

Uptime %:
UptimePercent = ([MillisecondsInMonth] - [Downtime]) / [MillisecondsInMonth]

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!