How to get latest value of each day an sum it up

[Jaenna]

Hi all,

I am struggling with the following issue:
I have a table with values that have been put in the system at some point but might have been altered over time. Not a big deal. But sometimes values are changed several times a day.

Now my question:

How do I extract only the latest value of each day and sum them up with other values for other "Schlüssel" for the same day? What I want in the end is a visual over time showing a cummulation of all values (of each "Schlüssel") per day as daily change over time.
Here is an example:

What i expect for "Schlüssel"=BDAS10119 are only the values 3, 5 and 13 (8 was put in on the same day as 13 but was replaced and should not count) and for BDAS-10191 only 3.

Then I want a visual over time (daily) giving me the sum of those values (There is more data in my original table, so there will be more values for different "Schlüssel" for the same day.)

I was trying out a lot, but nothing gave me the correct outcome.

Any ideas? 🙂

Best regards and thank you!
Jana 

[DataInsights · Accepted solution]

@Jaenna,

One option is to create a flag using a calculated column:

Is Latest Row = 
VAR vSchlussel = 'Table'[Schlüssel]
VAR vDate =
    INT ( 'Table'[Timestamp] )
VAR vMaxTimestamp =
    CALCULATE (
        MAX ( 'Table'[Timestamp] ),
        ALL ( 'Table' ),
        'Table'[Schlüssel] = vSchlussel,
        INT ( 'Table'[Timestamp] ) = vDate
    )
VAR vResult =
    IF ( 'Table'[Timestamp] = vMaxTimestamp, 1 )
RETURN
    vResult

This will enable you to create a measure like this:

Sum Latest Row = 
CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Is Latest Row] = 1 )

[xifeng_L · Accepted solution]

Hi @Jaenna ,

You can use the DATEVALUE function to add a date calculation column and then try the following measure.

Measure = 
SUMX(
    ALL('Table'[Product]),
    LASTNONBLANKVALUE('Table'[DateTime],SUM('Table'[Sales]))
)

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

[DataInsights · Accepted solution]

@Jaenna,

One option is to create a flag using a calculated column:

Is Latest Row = 
VAR vSchlussel = 'Table'[Schlüssel]
VAR vDate =
    INT ( 'Table'[Timestamp] )
VAR vMaxTimestamp =
    CALCULATE (
        MAX ( 'Table'[Timestamp] ),
        ALL ( 'Table' ),
        'Table'[Schlüssel] = vSchlussel,
        INT ( 'Table'[Timestamp] ) = vDate
    )
VAR vResult =
    IF ( 'Table'[Timestamp] = vMaxTimestamp, 1 )
RETURN
    vResult

This will enable you to create a measure like this:

Sum Latest Row = 
CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Is Latest Row] = 1 )