Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I am trying to calculate a KPI for each patient, the KPI is called "Initial prescription start date(IPST)".
The definition of IPST is if the patient has a negative history of using that particular medication for 60 days before a start date that start date is a IPST.
For example- See screen shot below, for patient with ID=101, I will start with IPST as 4/15/2019 , the difference in days between 4/15/2019 and 4/1/2019 is 14 <60 thus I will change my IPST to 4/1/2019.
Continuing with this iteration IPST for 101 is 3/17/2019 and 102 is 3/18/2018 as shown on the right hand side
table.
Can someone help to implement this logic in power BI. Looks like I will need to implement a loop, not sure how to do that using power bi.
Solved! Go to Solution.
Hey @kdesai ,
besides my question, about the date for patient ID 102,
Here you will find a pbix file that contains some sample data:
The DAX for column theDate:
theDate = var thisPatientID = 'Table1'[Patient] var thisDate = 'Table1'[Date] var theThresholdDate = MAXX( FILTER( ADDCOLUMNS( ADDCOLUMNS( SUMMARIZE( FILTER( ALL('Table1') ,'Table1'[Patient] = thisPatientID ) ,Table1[Patient] ,Table1[Date] ) ,"succeeding date", CALCULATE( MIN('Table1'[Date]) ,var newDate = [Date] return FILTER( ALL(Table1) ,[Patient] = thisPatientID && [Date] > newDate ) ) ) ,"days", DATEDIFF([Date],[succeeding date],DAY) ) ,[days] >= 60 ) ,[Date] ) return MINX( FILTER( ADDCOLUMNS( ADDCOLUMNS( SUMMARIZE( FILTER( ALL('Table1') ,'Table1'[Patient] = thisPatientID ) ,Table1[Patient] ,Table1[Date] ) ,"succeeding date", CALCULATE( MIN('Table1'[Date]) ,var newDate = [Date] return FILTER( ALL(Table1) ,[Patient] = thisPatientID && [Date] > newDate ) ) ) ,"days", DATEDIFF([Date],[succeeding date],DAY) ) ,[Patient] = thisPatientID && [Date] > theThresholdDate ) ,[Date] )
is addmittedly a more complex DAX statement, but this is due to the hidden complexities of your question.
From my understanding you are looking for the date that is the 1st date after a period if 60 days.
Here you will see a little screenshot:
Please be aware that I used different values for the Patient column. The columns "the date diff" and "the succeeding date" are not relvant. I just used them to check intermediate results.
Regards,
Tom
Hey,
can you please explain why the data for patient 102 is 2018-03-18 and not 2019-03-15?
Regards,
Tom
Hey @kdesai ,
besides my question, about the date for patient ID 102,
Here you will find a pbix file that contains some sample data:
The DAX for column theDate:
theDate = var thisPatientID = 'Table1'[Patient] var thisDate = 'Table1'[Date] var theThresholdDate = MAXX( FILTER( ADDCOLUMNS( ADDCOLUMNS( SUMMARIZE( FILTER( ALL('Table1') ,'Table1'[Patient] = thisPatientID ) ,Table1[Patient] ,Table1[Date] ) ,"succeeding date", CALCULATE( MIN('Table1'[Date]) ,var newDate = [Date] return FILTER( ALL(Table1) ,[Patient] = thisPatientID && [Date] > newDate ) ) ) ,"days", DATEDIFF([Date],[succeeding date],DAY) ) ,[days] >= 60 ) ,[Date] ) return MINX( FILTER( ADDCOLUMNS( ADDCOLUMNS( SUMMARIZE( FILTER( ALL('Table1') ,'Table1'[Patient] = thisPatientID ) ,Table1[Patient] ,Table1[Date] ) ,"succeeding date", CALCULATE( MIN('Table1'[Date]) ,var newDate = [Date] return FILTER( ALL(Table1) ,[Patient] = thisPatientID && [Date] > newDate ) ) ) ,"days", DATEDIFF([Date],[succeeding date],DAY) ) ,[Patient] = thisPatientID && [Date] > theThresholdDate ) ,[Date] )
is addmittedly a more complex DAX statement, but this is due to the hidden complexities of your question.
From my understanding you are looking for the date that is the 1st date after a period if 60 days.
Here you will see a little screenshot:
Please be aware that I used different values for the Patient column. The columns "the date diff" and "the succeeding date" are not relvant. I just used them to check intermediate results.
Regards,
Tom
User | Count |
---|---|
95 | |
87 | |
78 | |
72 | |
67 |
User | Count |
---|---|
111 | |
104 | |
84 | |
65 | |
63 |