Forum Discussion

M_SBS_6's avatar
M_SBS_6
Helper V
2 years ago
Solved

Measure based on latest date

Hi, 

I have a date and value column within my table. 

 

I have a card and within that, I need the value based on the latest date. I could have 5 rows of data linked to one date so need to sum them up. 

 

Example below, my card would return a value of 1800.

 

Date. Desc.  Value

10/07/2024  test1.  1000

10/07/2024. Test2.   800

09/07/2024  test1.   600

09/07/2024. Test 2.  300

Need Help
  • manvishah17's avatar
    manvishah17
    2 years ago

    Hi M_SBS_6 ,
    You can use either measure or calculated column ,
    Measure :

     

    LatestDateValueSum = 
CALCULATE(
    SUM('Table'[Value]),
    FILTER(
        'Table',
        'Table'[Date] =  MAX('Table'[Date])
    )

     

    or 
    Create a calculated column to rank the dates so you can identify the latest date:


    DateRank =
    RANKX(
    ALL('Table'[Date]),
    'Table'[Date],
    ,
    DESC,
    DENSE
    )

    Create a measure to sum the values for the latest date by filtering the data based on the rank:

    LatestDateValueSum =
    CALCULATE(
    SUM('Table'[Value]),
    FILTER(
    'Table',
    'Table'[DateRank] = 1
    )
    )

     

3 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    2 years ago

    Hi M_SBS_6 - can you try below measure to get the data as per latest date  

     

    Measure used:

    Latest Date Value =
    VAR LatestDate = MAX('Latest'[Date])
    RETURN
    CALCULATE(
        SUM('Latest'[Value]),
        'Latest'[Date] = LatestDate
    )

     

     

     

    It works

     

    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!!

  • manvishah17's avatar
    manvishah17
    Solution Supplier
    2 years ago

    Hi M_SBS_6 ,
    You can use either measure or calculated column ,
    Measure :

     

    LatestDateValueSum = 
CALCULATE(
    SUM('Table'[Value]),
    FILTER(
        'Table',
        'Table'[Date] =  MAX('Table'[Date])
    )

     

    or 
    Create a calculated column to rank the dates so you can identify the latest date:


    DateRank =
    RANKX(
    ALL('Table'[Date]),
    'Table'[Date],
    ,
    DESC,
    DENSE
    )

    Create a measure to sum the values for the latest date by filtering the data based on the rank:

    LatestDateValueSum =
    CALCULATE(
    SUM('Table'[Value]),
    FILTER(
    'Table',
    'Table'[DateRank] = 1
    )
    )

     

  • AnkitaaMishra's avatar
    AnkitaaMishra
    Super User
    2 years ago

    Hi M_SBS_6 ,

    please try below DAX : 

    Total_for_Max_Date =
    CALCULATE(
        SUM(Example[Value]),
        FILTER(
            Example,
            Example[Date] = MAX(Example[Date])
        )
    )


    If this works for you, please accept it as solution.

    Thanks,

    Ankita