topic Re: Latest value per day per group in DAX Commands and Tips

Latest value per day per group

Anonymous — Sun, 31 Jan 2021 14:51:46 GMT

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

Re: Latest value per day per group

AlB — Sun, 31 Jan 2021 15:07:40 GMT

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.

Re: Latest value per day per group

Anonymous — Sun, 31 Jan 2021 15:55:43 GMT

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

Re: Latest value per day per group

AlB — Sun, 31 Jan 2021 17:51:51 GMT

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.

Re: Latest value per day per group

Anonymous — Mon, 01 Feb 2021 04:46:59 GMT

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