Hello @Beth_H,
Can you please try this approach:
1. Create a date table that covers the range of your data
let
StartDate = #date(2024, 1, 1),
EndDate = #date(2024, 12, 31),
DateList = List.Dates(StartDate, Duration.Days(EndDate - StartDate) + 1, #duration(1, 0, 0, 0)),
DateTable = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}),
TypeChanged = Table.TransformColumnTypes(DateTable,{{"Date", type date}})
in
TypeChanged
2. Then, expand each placement record into individual months during which the placement is active. (Ensure the relationship between your date table and your transformed placements table exists)
let
Source = YourPlacementTable,
AddMonths = Table.AddColumn(Source, "Months", each {Number.From([start date])..Number.From([end date])}),
ExpandMonths = Table.ExpandListColumn(AddMonths, "Months"),
AddYearMonth = Table.AddColumn(ExpandMonths, "YearMonth", each Date.ToText(Date.From([Months]), "yyyy-MM")),
RemoveUnnecessaryColumns = Table.RemoveColumns(AddYearMonth,{"Months", "start date", "end date"})
in
RemoveUnnecessaryColumns
3. Finally, create a measure to count the active placements for each month.
Active Placements =
CALCULATE(
COUNTROWS(YourTransformedPlacementTable),
FILTER(
YourTransformedPlacementTable,
YourTransformedPlacementTable[YearMonth] = FORMAT(MAX('DateTable'[Date]), "yyyy-MM")
)
)
Hope this helps!
