Help with a KPI calulation in Power Bi

[kdesai]

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.

[TomMartens]

Hey, 

can you please explain why the data for patient 102 is 2018-03-18 and not 2019-03-15?

Regards,

Tom

[TomMartens · Accepted 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