Calculation to find previous date value for every date present in the dataset

[ankita_7]

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.

[harshnathani]

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

)

RETURN

b

Previous Value =

var _previousDate = [Previous Date]

return

CALCULATE(SUM(Table[Values]),FILTER(ALLEXCEPT(Table,Table[Patient No]),Table[Date] = _previousDate))

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!

[ankita_7]

Thank 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.

[harshnathani]

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 )

)

RETURN

b

then use this measure

Previous 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 Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!

[ankita_7]

Thanks.We tried this but not giving the lastest value it is giving the max value. How can we use timestamp in date column to get latest value from value column. Here below :

Previous 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
        )
    ).

[harshnathani]

Hi @ankita_7 ,

Can you share sample data in table format (not an image) and expected output to help you better.

Regards,

Harsh Nathani

[ankita_7]

Sure. I will send you by 9:30 IST the dataset.

[harshnathani]

Hi @ankita_7 ,

Please do check your Data Type and let me know if the measures are working.

Thanks,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!

[ankita_7]

I coverted the date to datetime but still the measure is not able to pick latest value. Logic looks fine to me but I think the measure is picking up only date max and not with max time for latest value. Please check the screenshots attached below:

[harshnathani · Accepted solution]

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 these

Use 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
b

Previous 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!!

[ankita_7]

Thank you so much for all the help and quick response @harshnathani . This solution is working!