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

v-xiaotang's avatar
v-xiaotang
Community Support
4 years ago

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.