Count based on max date

[ben-t]

Hi all , 
I'm trying to do a distinct count of locations based on their last status date.
the locations need to fufil 3 criteria, prefix, dept and changedate before the max slicer date selected
Eg: for July, both locations fufil all 3 criteria, so count should be 2

but for august, location 702 should be counted, but 701 should not be. 

How can i use a measure to count rows based on max change date < max selected dates? Thanks

location count = CALCULATE(distinctCOUNT(Locations[locations.location]),FILTER(Locations,Locations[locations.prefix] in ALLSELECTED('dept prefix'[Prefix]) && Locations[locations.department] in ALLSELECTED('dept prefix'[Department]) && MAX(Locations[locations.changedate])<= MAX('Date Table'[Date])))

[v-xiaotang]

Hi @ben-t 

Have you solved your problem? If yes,  could you kindly mark the answer helpful as the solution? 
If not, could you provide more details about your problem? so that we can work on it further. Thanks.

Best Regards,
Community Support Team _ Tang

[v-xiaotang]

Hi @ben-t 

I think your measure is right, for august, one row of location 701 fullfils 3 criteria, but another not. so the distinctCOUNT of location should be 2. (row2 & row3)

If I miss anything, please let me know. and could you share your sample file? so that we can check the measure further.

Best Regards,

Community Support Team _Tang

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

[ben-t]

Or is it possible to create a dax table where i can have distinct rows of each location, based on the max status date < max slicer date? 

[amitchandak · Accepted solution]

@ben-t , if date table is not joined

new measure =
var _date = MAXX(allselected('Date Table'), 'Date Table'[Date])
return
CALCULATE(distinctCOUNT(Locations[locations.location]),FILTER(Locations, Locations[locations.changedate]<= _max))

if joined with date on change date

new measure =
var _date = MAXX(allselected('Date Table'), 'Date Table'[Date])
return
CALCULATE(distinctCOUNT(Locations[locations.location]),FILTER(all(Date Table'), 'Date Table'[Date]<= _max))