Please Urgent Help with Creating Period blocks from Start Date

[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-yueyunzh-msft · Accepted solution]

Hi, @yve214 

According to your description , you want to display the visual you provide in the last of this article.

For the output data you provide, I found that every date interval was inconsistent, some 30 days and some 29 days.One thing to be clear is that we have to make sure that this date interval is consistent, and here I test it according to the rules you provide (14, 30, 60, 90).

Here are the steps you can follow:

(1)This is my test data: ‘Sheet1’ and 'Sheet4'

(2)We can extend the table 'Sheet1' in Power Query Editor:

let
    Source = Excel.Workbook(File.Contents("C:\Users\v-yueyzhang\Downloads\case\case.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    test = Table.TransformColumnTypes(#"Promoted Headers",{{"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}}),
    #"Added Custom1" = Table.AddColumn(test, "frequency_days", (x)=> if x[frequency_type] = "Monthly" then 30 else if x[frequency_type] = "Bi-Monthly" then 60 else if  x[frequency_type]="Weekly" then 14 else  90),
    #"Added Custom" = Table.AddColumn(#"Added Custom1", "Series", (x)=> Table.AddColumn(Table.FromColumns({List.Dates( x[start_date] ,Number.RoundUp(Number.From((x[end_date]-x[start_date])/x[frequency_days])), #duration(x[frequency_days],0,0,0) )}),"end",(y)=>if Date.From(Number.From(y[Column1])+29) > x[end_date] then x[end_date] else Date.From(Number.From(y[Column1])+x[frequency_days]-1)    )                   ),
    #"Expanded Series" = Table.ExpandTableColumn(#"Added Custom", "Series", {"Column1", "end"}, {"from_date", "to_frequency"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Series",{{"from_date", type date}, {"to_frequency", type date}})
in
    #"Changed Type"

The result in Power Query Editor is :

(3)Then we Apply it to Power BI Desktop, we can create two measures :

Actual = var _current_id=SELECTEDVALUE('Sheet1'[patient_id])
var cuurent_from=SELECTEDVALUE('Sheet1'[from_date])
var current_to=SELECTEDVALUE('Sheet1'[to_frequency])
var _t=FILTER('Sheet4','Sheet4'[patient_id]=_current_id && 'Sheet4'[date] >= cuurent_from && 'Sheet4'[date] <= current_to && 'Sheet4'[is_attended]=1)
return 
COUNTROWS(_t)+0

% Actual to Target(freq) = DIVIDE(  [Actual]  , SUM('Sheet1'[expected_frequency]))

(4)Then we can put the measures and the fields in the visual we need so that we can meet your need, the result is as follows:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly