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

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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

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

        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: