Forum Discussion

JCK2's avatar
JCK2
Helper III
3 years ago
Solved

Get latest value based on max date

Hello!!!

 

I want to get the latest target based on the latest date and for the date format has time also, so basically the latest value based on max date and time.

 

The expeted output of this is 95, i have used max date funcation but it does not seem to work.

 

Project NameModified DateTarget
BASIC13-Dec-22 14:00     95
BASIC

13-Dec-22

13:00

    90
BASIC

13-Dec-22

12:00

   85

 

Any suggest, how to get this working? Thanks a lot in advance!!

 

 

 

DAX
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    hi JCK2 

     

    try to plot a visual table with a measure of this:

    Target2 =
    VAR _value = MAXX(TableName, TableName[ModifiedDate])
    RETURN
    MAXX(
        FILTER(TableName, TableName[ModifiedDate] =_value),
        TableName[Target]
    )
     
    i tried and it worked like this:

    the dataset:

     

8 Replies

  • JW_van_Holst's avatar
    JW_van_Holst
    Resolver IV
    3 years ago
    _TargetMaxDate = 
VAR __filer = MAX('Table'[Modified Date])
VAR __target = CALCULATE(SELECTEDVALUE('Table'[Target]), 'Table'[Modified Date] = __filer)
RETURN
__target

     

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    hi JCK2 

     

    try to plot a visual table with a measure of this:

    Target2 =
    VAR _value = MAXX(TableName, TableName[ModifiedDate])
    RETURN
    MAXX(
        FILTER(TableName, TableName[ModifiedDate] =_value),
        TableName[Target]
    )
     
    i tried and it worked like this:

    the dataset:

     

    • FreemanZ's avatar
      FreemanZ
      Super User
      3 years ago

      it can be further simplified like this:

      Target3 =
      VAR _value = MAX(TableName[ModifiedDate])
      RETURN
      MAXX(
          FILTER(TableName, TableName[ModifiedDate] =_value),
          TableName[Target]
      )
    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      With this I got What I wanted but one thing when I enable the total values the total the value is coming as Max value of both(in this case 95)  instead of sum of both ( 96) . 
      Can you please help me with that.

  • philouduv's avatar
    philouduv
    Resolver III
    3 years ago

    Hello JCK2 ,

    If you only want the target create this measure to give you the last value of target:

    If you also want the latest date associated create an other measure like this 



    Hope it helps you

    • Walt1010's avatar
      Walt1010
      Helper V
      1 year ago

      This (the Related Target calc) looks very promising, but when I try it I get the error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

       

      Its confusing becasue the condition in my case definitely can only be satisfied by a single Date, and hence a single related value. I tried  dividing it and first retreieving the latest data using LASTDATE, and then using that date to get the value, and it seemed to work.

  • latimeria's avatar
    latimeria
    Solution Specialist
    3 years ago

    Hi JCK2 ,

     

    Something like this:

    //get max date for 1 project
    VAR MaxDate = calculate( max('table'[Modified Date]), allexcept('table, 'table'[project name]))
    RETURN

    //get target for max date for the project. Instead of max, you can use min as you retrieve only 1 value

    //assumpton: you have a relationship between table date & project date
    calculate( max('table'[Target]), datetable[date] = MaxDate)

  • JCK2's avatar
    JCK2
    Helper III
    3 years ago

    Thanks everyone! let me try them 🙂