Please Urgent Help with Creating Period blocks from Start Date Column

[yve214]

Hello,

Please, I desperately need help with any tips or recommendations on how to target this problem.

I have one requirement in a cencus report I built to add actuals to target services of patients. What I mean by this is,

if I have a patient who is required to have their services done 2 times monthly, I would hope from the start date to 30 days from that start date, they have been attended to, 2 times giving me 100% of my actual to target.

So again, if my expected services to be done is set to be 2 services monthly, my actual services in that time period from the start date e.g. 1/1/2022 - 1/31/2022  is 1, then that I would have been attended to just 50% of the time. 

Is there a way anyone could advise me how to target this problem given the below data, and if so, would you recommend some ideas please? I really need help with how I can build some of this period blocks so if its

- monthly, +30 days from start date

- weekly, +14 days from start date

-bi-monthly, +60 days from start date

- quarterly, +90 days from start date and so on

I had to model both of these fact tables in power bi. I can also perform a join in the database to merge all to one, of which I plan to do. Its just a bit challenging considering the frequency table has a start & end date, and the service table has just a calendar date. I guess I can join both tables on their patient_id and service_date where it falls between the from and to date columns in the frequency table.

But please advise, anything would be greatly appreciated. I really need inputs on how to get this one visual out. THANK YOU so much in advance.

Sample raw data

frequency_table:

patient_id	patient_name	start_date	end_date	expected_frequency	frequency_code	frequency_type
1001	Sara S	6/18/2020	12/31/2020	2	3	Monthly
1001	Sara S	1/1/2021	3/1/2021	1	4	Bi-Monthly

service_table: is_attended code 1 for Yes, 0 for No. 

patient_id	patient_name	date	is_attended
1001	Sara S	6/18/2020	1
1001	Sara S	6/30/2020	1
1001	Sara S	7/18/2020	1
1001	Sara S	7/31/2020	0
1001	Sara S	8/18/2020	1
1001	Sara S	8/31/2020	0
1001	Sara S	9/18/2020	1
1001	Sara S	9/30/2021	1
1001	Sara S	10/18/2021	1
1001	Sara S	10/29/2021	1
1001	Sara S	11/18/2021	0
1001	Sara S	12/5/2021	0
1001	Sara S	12/18/2021	0
1001	Sara S	12/27/2021	0
1001	Sara S	1/1/2021	1
1001	Sara S	2/1/2021	0
1001	Sara S	3/15/2021	0

Expected Results: to_frequency_date is a sample column for the period date from the frequency "start_date". 

patient_id	patient_name	from_date	to_frequency_date	expected_frequency	Actual	frequency_type	% Actual to Target(freq)
1001	Sara S	6/18/2020	7/17/2020	2	2	Monthly	100%
1001	Sara S	7/18/2020	8/17/2020	2	1	Monthly	50%
1001	Sara S	8/18/2020	9/17/2020	2	1	Monthly	50%
1001	Sara S	9/18/2020	10/17/2020	2	2	Monthly	100%
1001	Sara S	10/18/2020	11/17/2020	2	2	Monthly	100%
1001	Sara S	11/18/2020	12/17/2020	2	0	Monthly	0%
1001	Sara S	12/18/2020	12/31/2020	1	0	Monthly	0%
1002	Sara S	1/1/2021	3/1/2021	1	1	Bi-Monthly	100%

Please let me know if you have any questions, happy to clarify.

[v-easonf-msft · Accepted solution]

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