Count Latest Date

[Anonymous]

Hi, I have another Dax Measure problem.

I need to create measure, which counts Manager Status in the latest date for each manager ID, additionaly date slicer available on the page should impact that measure.

My data source :

is like that:

Date	ManagerID	Status
18.08.2020	1	IRE
18.08.2020	2	GER
18.08.2020	5	ASI
18.08.2020	3	ENG
31.09.2019	1	IRE
31.09.2019	4	SWI
31.09.2019	2	GER
12.08.2015	1	IRE
12.08.2015	2	GER
11.09.2018	3	ENG

Now i want to count how many IRE/ GER / ENG/ ASI/SWI Managers we do have in the latest date for each manager ID- so from above example how it looks for the latest date for each manager ID. So the table would have staus on axis then count of how many managers fit into each so based on the data above you would have 1 of each of IRE/GER/ENG/ASI/SWI as even though the latest date may not be 18.08.2020 for SWI it would still be included as for that manager ID thats the latest status for him.

What's more Date filter which is applied as a slicer on the report page should filter that date?

Could you please help me ?

I tried with something like that : 

Measure = CALCULATE(COUNT('Manager Ratings'[Status]),MAX('Table'[Date]) , but MAX does not work in this case.

Thanks in advance!

[Anonymous · Accepted solution]

Hi,

Please check the measure.

Measure = CALCULATE(COUNT('Table'[ManagerID]),FILTER(ALLEXCEPT('Table','Table'[Status]),'Table'[Date]=MAXX(ALL('Table'),'Table'[Date])))

Result would be shown as below.

 

Best Regards,

Jay

[Tahreem24]

Try this out:

Measure = CALCULATE(COUNT('Manager Ratings'[Status]),FILTER(Table, Table[Date]=MAX('Table'[Date]))) 

[Anonymous]

@Tahreem24thank you for your reply, this is what i have tried although this seems to just look at the latest date by status. Whereas i would need to look at this per Manager ID? so i could create a bar chart to say satus as the axis and the count to be based on the latest date per manager ID how many fit into each status. Does that make sense?

[VahidDM]

Hi,

First, September does not have day 31!

second, your question is not totally clear for me, but please check this measure:

measure = 
VAR _MaxDate =
    CALCULATE ( MAX ( 'Table'[Date] ), FILTER ( 'Table', 'Table'[Manager ID] ) )
RETURN
    CALCULATE ( COUNT ( 'Table'[Date] ), 'Table'[Date] = _MaxDate ) 

Result would be shown as below:

Don't forget to give thumbs up and accept this as a solution if it helped you!!!