Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Count rows before range set in date slicer

Hi all,

 

I am using a date 'Between' slicer to control many visuals in my report, it is using Calendar[Date].
In one of my table visuals I would like to create a column that includes a count of all rows in my 'Opportunity' table where the 'SubmissionDate' is less that the start date of my slicer, irrespective of whether any other date within the row falls within the date range set.

 

If any other information is required to resolve this request please let me know.

 

Thanks

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous 

    You can use crossfilter() function, you can refer to the following sample

    Sample data 

    Date table

     

    Relationship

     

    Then create a measure

    Measure = CALCULATE(COUNTROWS('Table'),'Table'[Column2]<MIN('Table 2'[Date]),CROSSFILTER('Table 2'[Date],'Table'[Column2],None))

    Output

    Best Regards!

    Yolo Zhu

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

     

     

4 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thanks for your reply, the ALL function looks good, however I would still need to use the start date of the slicer to be able to filter all rows where SubmissionDate is less is than that start date.

       

      How would I manage this?

       

      Thanks

      • ryan_mayu's avatar
        ryan_mayu
        Super User
        3 years ago
        could you pls provide some sample data and the expected output?
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous 

    You can use crossfilter() function, you can refer to the following sample

    Sample data 

    Date table

     

    Relationship

     

    Then create a measure

    Measure = CALCULATE(COUNTROWS('Table'),'Table'[Column2]<MIN('Table 2'[Date]),CROSSFILTER('Table 2'[Date],'Table'[Column2],None))

    Output

    Best Regards!

    Yolo Zhu

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