Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Help with a KPI calulation in Power Bi

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.

Need Help
  • TomMartens's avatar
    TomMartens
    7 years ago

    Hey Anonymous ,

     

    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

     

2 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    7 years ago

    Hey, 

     

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

     

    Regards,

    Tom

    • TomMartens's avatar
      TomMartens
      Super User
      7 years ago

      Hey Anonymous ,

       

      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