Forum Discussion
Helper IICalculation to find previous date value for every date present in the dataset
I have a table that has value,ptient ID ,date,etc columns. I'm facing a challenge in finding previous weight value for every date for that particular patient ID. Suppose I 'm checking today's date then there should be a column that gives me weight for today and a column that shows weights on previous date if exist. I have a column named value that consist of weight values so this weight values for a particular Patient ID will be on different dates available. I want to display a tuple that has patient id, its weights by dates and previous weight.
I have attached a screenshot of the dataset.
I want to add a column more to this dataset that will have previous value from value column for each date.
Please can anybody help me in solving this math.
Thank you in advance.
Hi,
1. Check the Data Type in Power Query . It should be Date Time.
2. Check whether any values are getting summarised by default in the pane. Make all as Don't Summarize.
3.Change Format of all your Date/Time column to the same one. Choose a format which has DD/MM/YYYY hh:mm:ss AM/PM since your data involves all of theseUse these Measures:
Previous Patient No2 =
VAR a =
MAX ( 'Table6'[Date] )
VAR b =
CALCULATE (
MAX ( Table6[Patient No] ),
FILTER (
ALL (
Table6[Patient No],
Table6[Date],
Table6[Values]
),
Table6[Date] < a
&& Table6[Patient No]
= MAX ( Table6[Patient No] )
)
)
RETURN
b
Previous Date2 =
VAR a =
MAX ( 'Table6'[Date] )
VAR b =
CALCULATE (
MAX ( Table6[Date] ),
FILTER (
ALL (
Table6[Patient No],
Table6[Date],
Table6[Values]
),
Table6[Date] < a
&& Table6[Patient No]
= MAX ( Table6[Patient No] )
)
)
RETURN
bPrevious Value2 =
VAR _previousDate = [Previous Date2]
VAR _patientno = [Previous Patient No2]
RETURN
CALCULATE (
MAX ( Table6[Values] ),
FILTER (
ALL ( Table6 ),
Table6[Date] = _previousDate
&& Table6[Patient No] = _patientno
)
)It is working fine for me with Data Type Date Time.
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!
10 Replies
ankita_7 ,
Try This measure.
Previous Date =VAR a =MAX ( Table[Date] )VAR b =CALCULATE (MAX ( Table[Date] ),FILTER ( ALLEXCEPT(Table,Table[Patient No]), Table6[Date] < a ))RETURNbPrevious Value =var _previousDate = [Previous Date]returnCALCULATE(SUM(Table[Values]),FILTER(ALLEXCEPT(Table,Table[Patient No]),Table[Date] = _previousDate))Regards,Harsh NathaniDid I answer your question? Mark my post as a solution! Appreciate with a Kudos!!
ankita_7Thank you. I was able to calculate the previous value when I changed sum() to max().But there's a problem, I want to calculate latest value on that date like the last value according to the timestamp. The date column is a datetime type column so I have date and time both. But I used max function to calculate single value so it is giving me max value for that date and not latest.What can I do to get the last value on that date?The logic you gave is perfectly working for the dates that have single value of max value as latest value.
Hi ankita_7 ,
Create a measure
Previous Patient No =VAR a =MAX ( Table6[Date] )VAR b =CALCULATE (MAX ( Table6[Patient No] ),FILTER ( ALLEXCEPT(Table6,Table6[Patient No]), Table6[Date] < a ))RETURNbthen use this measurePrevious Value =
VAR _previousDate = [Previous Date1]
VAR _patientno = [Previous Patient No]
RETURN
CALCULATE (
MAX ( Table6[Values] ),
FILTER (
ALL ( Table6 ),
Table6[Date] = _previousDate
&& Table6[Patient No] = _patientno
)
)Regards,Harsh NathaniDid I answer your question? Mark my post as a solution! Appreciate with a Kudos!!