Forum Discussion

M_SBS_6's avatar
M_SBS_6
Icon for Helper V rankHelper 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...
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
    )
    )

     

manvishah17's avatar
manvishah17
Icon for Solution Supplier rankSolution 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
)
)