Forum Discussion

ben-t's avatar
ben-t
Frequent Visitor
4 years ago
Solved

Count based on max date

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...
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    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))

ben-t's avatar
ben-t
Frequent Visitor
4 years ago

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's avatar
amitchandak
Super User
4 years ago

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))