Find date different between 2 rows in PowerBI-CodePudding

patient_id  treatment_date
100         4/28/2018
100         4/27/2019
100         4/29/2019
100         6/22/2019
101         3/6/2021
101         1/1/2022
101         4/6/2022
102         6/2/2017
102         6/6/2018
102         10/7/2018
102         9/4/2021
102         6/2/2022

Hi everyone,

I have a table which recorded the date when the patients came back for treatment as shown in the data above. I want to calculate the number of days from the first treatment (first date) for their subsequent treatment for each and every patient.

The expected outcome is shown in the screenshot above in the 3rd column days_from_first_treatment. I'm fine with either using DAX or Power query. Any help or advise will be greatly appreciated!

Edit

CodePudding user response：

try this measure...

Days From First Treatment  =
VAR _first =
    CALCULATE (
        MIN ( 'Table'[treatment_date] ),
        ALLEXCEPT ( 'Table', 'Table'[patient_id] )
    )
VAR _date =
    SELECTEDVALUE ( 'Table'[treatment_date] )
RETURN
    CALCULATE (
        DATEDIFF ( _first, _date, DAY ),
        ALLEXCEPT ( 'Table', 'Table'[patient_id] )
    )

CodePudding user response：

Column

VAR currenDate = CALCULATE(MAX(tbl[treatment_date]))
VAR prevDate = 
        MINX(
            CALCULATETABLE(tbl,ALLEXCEPT(tbl,tbl[patient_id]))
            ,'tbl'[treatment_date]
        )
RETURN 
    INT(currenDate-prevDate)

NEW solution:

VAR currenDate = [treatment_date]
VAR patId=[patient_id]
VAR prevDate = 
        MINX(
            FILTER(ALL(tbl),[patient_id]=patId)
            ,[treatment_date]
        )
RETURN 
    INT(currenDate-prevDate)

CodePudding user response：

In M you could use,

let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"patient_id", Int64.Type}, {"treatment_date", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"patient_id"}, {{"data", each let min=Number.From(List.Min(_[treatment_date])) in Table.AddColumn(_, "Custom", each Number.From([treatment_date])-min), type table}}),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", { "treatment_date", "Custom"}, { "treatment_date", "days_from_first_treatment"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded data",{{"days_from_first_treatment", Int64.Type}, {"treatment_date", type date}})
in  #"Changed Type1"