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

4 Replies

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

  • 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.

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

    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