Forum Discussion

bv2023's avatar
bv2023
New Member
3 years ago
Solved

Return the latest value by date and condition

Hello,

 

I would like to have a PowerBI Card or Multi-row Card show the latest value from a table based on time and other conditions.

 

I have the following table from which I will be pulling information:

strnamedatstampfltvalue
C1B7/27/2023 0:011
C1B7/27/2023 0:025
C1B7/27/2023 0:036
C1B7/27/2023 0:044
C1B7/27/2023 0:052
C2B7/27/2023 0:011
C2B7/27/2023 0:024
C2B7/27/2023 0:035
C2B7/27/2023 0:049
C2B7/27/2023 0:052

 

I am having trouble coming up with a measure that will return fltvalue where the strname=C1B and the most recent date. Could someone help me with the DAX code that creates a measure that will pull the most recent ftvalue for just C1B?

 

Need Help
  • bv2023's avatar
    bv2023
    3 years ago
    LATEST=

    VAR last_value = MAX(Data[datstamp])
    VAR tag = "C2A"

    Return
    MAXX(FILTER(ALL(Data),Data[datstamp]=last_value &&
    Data[strname]=tag),
    Data[fltvalue])

7 Replies

  • Arul's avatar
    Arul
    Icon for Super User rankSuper User
    3 years ago

    bv2023 ,

    Can you try this measure?

    Max value Per Strname = 
CALCULATE (
    MAX ( 'Table'[fltvalue] ),
    INDEX (
        1,
        ALL ( 'Table'[datstamp], 'Table'[strname] ),
        ORDERBY ( 'Table'[datstamp], DESC ),
        DEFAULT,
        PARTITIONBY ( 'Table'[strname] )
    )
)
    • bv2023's avatar
      bv2023
      New Member
      3 years ago

      The measure compiles but does not return ftvalue of 2 when inserted into a Card in PowerBI. How do I specify or filter to show only strname=C1B? 

      Thanks for your help Arul!

      • Arul's avatar
        Arul
        Icon for Super User rankSuper User
        3 years ago

        bv2023 ,

        added one filter from strname, Can you try now?

         

        Max value Per Strname = 
CALCULATE (
    MAX ( 'Table'[fltvalue] ),
    INDEX (
        1,
        ALL ( 'Table'[datstamp], 'Table'[strname] ),
        ORDERBY ( 'Table'[datstamp], DESC ),
        DEFAULT,
        PARTITIONBY ( 'Table'[strname] )
    ),'Table'[strname] = "C1B"
)

         

  • bv2023's avatar
    bv2023
    New Member
    3 years ago

    In words, the code needs to look at the table column [datstamp] where the date is maximum, ensure [strname]= 'C1B' and pull the value at [fltvalue]. Can someone else assist with this challenge? I do not know the syntax well enough.

  • bv2023's avatar
    bv2023
    New Member
    3 years ago
    LATEST=

    VAR last_value = MAX(Data[datstamp])
    VAR tag = "C2A"

    Return
    MAXX(FILTER(ALL(Data),Data[datstamp]=last_value &&
    Data[strname]=tag),
    Data[fltvalue])