Find date different between 2 rows in PowerBI

user_15002748 0
Viewed 61
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.

enter image description here

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

enter image description here

dax powerbi powerquery date
3 Answers

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] )
    )

Sample PBix File

sample

user_52602650

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)
user_190423790

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"
user_92642300
Related