Calculate last amount per hour

[feralvarez994]

Hi everyone! I need to get the last amount (MONTO column) of every BANK (BANCO/ENTIDAD column) by date and time (FECHA Y HORA Column).

I have a dax with CALCULATE with LASTDATE but it returns the first amount of the last day at the earlier hour, example if i have a value from today morning and another row with today afternoon, it only gets the first value(morning one) instead of the afternoon one

I need it to return the last value also having in consideration the hour

Can someone help me please? My dataset looks like this

Thanks everyone

[v-chenwuz-msft · Accepted solution]

Hi @feralvarez994,

You can use this code to get the result:

Measure =
VAR _S =
    SUMMARIZE (
        'Table',
        [BANCO/ENTIDAD],
        "LAST", CALCULATE ( SUM ( 'Table'[MONTO] ), TOPN ( 1, 'Table', [FECHAYHORA], DESC ) )
    )
RETURN
    SUMX ( _S, [LAST] )

Pbix file in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@feralvarez994 , Create a new column

Date hour = datevalue([Date Time]) + time(hour([Date Time]),0,0)

a new measure =

calculate(lastnonblankvalue(Table([Date Time], sum(table[Value]), allexcept(Table, Table[Date hour] ) )