Formula to Count only latest records

[Anonymous]

Hi team, 

I've spent quite a few days looking at similar requests to only count the latest values but cannot find a solution that returns the values I need to count.

I have a table for Meeting Minutes and I need to count only the actions raised in the last meeting held.

We can have the same date for different Meeting Names so I want to count the total value of the Actions raised for the maximum Meeting Number for that unique Meeting name.

Table looks something like this

Meeting Date	Meeting Name	Meeting Number	Item Number	Action raised
11/12/2020	Issues Meeting	88	131-01	1
11/12/2020	Issues Meeting	88	132-01	0
01/01/2021	Marketing Meeting	01	001-01	1
01/01/2021	Marketing Meeting	01	002-06	0
01/01/2021	Marketing Meeting	01	003-02	1
01/01/2021	Sales Meeting	35	008-01	1
01/01/2021	Sales Meeting	35	009-01	1
15/03/2021	Marketing Meeting	02	005-09	1
15/03/2021	Marketing Meeting	02	007-03	0
15/03/2021	Marketing Meeting	02	010-05	1
15/03/2021	Sales Meeting	36	001-01	1
15/03/2021	Sales Meeting	36	002-01	0
15/03/2021	Sales Meeting	36	002-02	1
19/03/2021	Marketing Meeting	03	022-01	1
19/03/2021	Marketing Meeting	03	031-00	1
19/03/2021	Marketing Meeting	03	035-01	1

I want to be able to drop the measture onto a CARD visual or similar to show

Total Issues Actions raised = 1

Total Marketing Actions raised = 3

Total Sales Actions raised = 2

I can't seem to put together a dax formula to count the total of the "Actions Raised" column based on the highest meeting number for a specific meeting name.

I would really appreciate any help with this!

[v-lionel-msft]

Hi @Anonymous ,

Has your problem been solved?

Best regards,
Lionel Chen

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

[v-lionel-msft · Accepted solution]

Hi @Anonymous ,

Or do it like this? You just need to create one measure, but you need to add the [Meeting Name] field to visualize the measure.

Measure = 
VAR maxdate =
    CALCULATE (
        MAX ( 'Table'[Meeting Date] ),
        FILTER(
           ALL( 'Table'),
            'Table'[Meeting Name] = MAX('Table'[Meeting Name])
        )
    )
RETURN
CALCULATE(
    SUM('Table'[Action raised]),
    FILTER(
        ALL('Table'),
        'Table'[Meeting Name] = MAX('Table'[Meeting Name]) &&
        'Table'[Meeting Date] = maxdate
    )
)

Best regards,
Lionel Chen

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

[Jihwan_Kim]

Hi, @Anonymous 

Please check the sample pbix file's link down below, whether it is what you are looking for.

All measures are in the sample pbix file.

https://www.dropbox.com/s/jpbg7dgqdsfzx8z/globet.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

[Anonymous]

Hi there, those measures look good but I have an unlimited number of meeting names (users can create new meetings at any time) so is there a way for the measure to compare meeting name and maximum meeting number to determine if the actions should be counted without needing to write out measures for hundreds of different meeting names?

[amitchandak]

@Anonymous , Try a measure like

Measure =
VAR __id = MAX ('Table'[IDU] )
VAR __date = CALCULATE ( MIN('Table'[DATE] ), ALLSELECTED ('Table' ), 'Table'[IDU] = __id )
CALCULATE ( Count ('Table'[Item Number] ), VALUES ('Table'[IDU] ),'Table'[IDU] = __id,'Table'[DATE] = __date )

[Anonymous]

Hi @amitchandak this looks good but in the visual it is only showing me 3 Meetings with Actions. I need to see all Meeting Names that have Actions raised. There are 57 different meeting names. 

[Anonymous]

Measure =

VAR __ID = MAX('05 MoM'[Meeting Number])

VAR __DATE = CALCULATE(

MIN('05 MoM'[Date]), ALLSELECTED('05 MoM'), '05 MoM'[Meeting Number] = __ID)

RETURN

CALCULATE(

COUNT('05 MoM'[Meeting Number]),

VALUES('05 MoM'),'05 MoM'[Meeting Number]=__ID,'05 MoM'[Date]=__DATE)