Latest value per day per group

[Anonymous]

Hi,

I have a table that shows data uploads per table (name) per day. Usually there is only one upload per table per day but in some cases table needs to be updated multiple times in one day. I would like to monitor the rowcount per table per day but these exceptions make it really hard. So, is there a way to filter the latest value per day per group (name)? You can find the pbix here.

Thank you in advance!

Julia

[Anonymous]

Oh what an embarrasing mistake. More coffee for me 😄 Thank you so much @AlB !

[AlB]

@Anonymous 

You need  to update the measure correctly. From the results it looks like you are using 'Table' instead of 'Table 2'.

Try:

Last day2 =
CALCULATE (
    SUM ( 'Table 2'[value] ),
    FILTER ( ( 'Table 2' ), 'Table 2'[date] = MAX ( 'Table 2'[date] ) )
)

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[AlB · Accepted solution]

Hi @Anonymous 

Create a new calculated table:

Table 2 = 
FILTER (
    'Table',
    VAR day_ =
        DATEVALUE ( 'Table'[date] )
    VAR latest_ =
        CALCULATE (
            MAX ( 'Table'[date] ),
            DATEVALUE ( 'Table'[date] ) = day_,
            ALLEXCEPT ( 'Table', 'Table'[name] )
        )
    RETURN
        latest_ = 'Table'[date]
)

 This could also be done in Power Query

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[Anonymous]

Hi,

Thanks! As a table your solution seems to be working fine but when adding a measure to get the information for the latest day I now only get the same value for each name. The same measure use to work with the original table.

The measure I'm using is: 

Last day = CALCULATE(

SUM('Table'[value]),

FILTER(('Table'), 'Table'[date] = Max('Table'[date])))

Julia