Forum Discussion

ankita_7's avatar
ankita_7
Icon for Helper II rankHelper II
6 years ago
Solved

Calculation 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 th...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    ankita_7
    Icon for Helper II rankHelper II
    6 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        ankita_7
        Icon for Helper II rankHelper II
        6 years ago

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