Please Urgent Help with Creating Period blocks from Start Date Column

Hi, yve214 

You can split the table into multiple sub-tables based on frequency_type, then convert each table rows into multiple interval rows for calculation and finally you can combine the results of multiple tables.

I made a sample (frequency type ="Monthly") for your reference.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMFTSUQpOLEpUCAYyzPQNLfSNDIwMgGxDI31jQxjHCIiNgdg3P68kI6dSKVYHU7OhPlg5SMwYwQRhEyB2ytSFa44FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [patient_id = _t, patient_name = _t, start_date = _t, end_date = _t, expected_frequency = _t, frequency_code = _t, frequency_type = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"patient_id", Int64.Type}, {"patient_name", type text}, {"start_date", type date}, {"end_date", type date}, {"expected_frequency", Int64.Type}, {"frequency_code", Int64.Type}, {"frequency_type", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([frequency_type] = "Monthly")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Month Span", each (12 * (Date.Year([end_date]) - Date.Year([start_date])))
+ (Date.Month([end_date]) - Date.Month([start_date]))
+ (if Date.Day([end_date]) < Date.Day([start_date]) 
    then -1 
    else 0
  )),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Month List", each List.Numbers(
  1,
  [Month Span]
)),
    #"Expanded Month list" = Table.ExpandListColumn(#"Added Custom1", "Month List"),
    #"Added Custom2" = Table.AddColumn(#"Expanded Month list", "from_date", each Date.AddMonths(
    [start_date],
     [Month List]-1
  )),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "to_date", each Date.AddDays(
Date.AddMonths( 
    [start_date], 
    [Month List] 
  ),-1)),
    #"Added Conditional Column" = Table.AddColumn(#"Added Custom3", "to_frequency_date", each if [Month Span] = [Month List] then [end_date] else [to_date]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"start_date", "end_date", "Month Span", "Month List", "to_date"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"patient_id", "patient_name", "from_date", "to_frequency_date", "expected_frequency", "frequency_code", "frequency_type"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"from_date", type date}, {"to_frequency_date", type date}})
in
    #"Changed Type1"

Actual frequency = 
CALCULATE (
    COUNT ( service_table[is_attended] ),
    FILTER (
        ALL(service_table),
        service_table[is_attended] = 1
            && service_table[date] >= 'frequency_table(monthly)'[from_date]
            && service_table[date] <= 'frequency_table(monthly)'[to_frequency_date]
    )
)

% Actual to Targe = 'frequency_table(monthly)'[Actual frequency]/'frequency_table(monthly)'[expected_frequency]

result：

 

Best Regards,
Community Support Team _ Eason