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
大家好,
我有一张表,上面的数据显示了病人回来接受治疗的日期。我想计算一下,从第一次治疗(第一次约会)开始,对每个病人进行后续治疗的天数。
预期的结果显示在上面第3列days_from_first_treatment
的屏幕截图中。我不介意使用DAX或Power查询。如有任何帮助或建议,将不胜感激!
编辑
发布于 2022-08-25 09:15:43
列
VAR currenDate = CALCULATE(MAX(tbl[treatment_date]))
VAR prevDate =
MINX(
CALCULATETABLE(tbl,ALLEXCEPT(tbl,tbl[patient_id]))
,'tbl'[treatment_date]
)
RETURN
INT(currenDate-prevDate)
新解决方案:
VAR currenDate = [treatment_date]
VAR patId=[patient_id]
VAR prevDate =
MINX(
FILTER(ALL(tbl),[patient_id]=patId)
,[treatment_date]
)
RETURN
INT(currenDate-prevDate)
发布于 2022-08-25 09:04:01
试试这个方法..。
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] )
)
发布于 2022-08-25 10:30:53
在M中你可以用,
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"
https://stackoverflow.com/questions/73484374
复制相似问题