Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Compare date between two tables and count

I have two date tables.

Main table has multiple date column, One column name is "date" another is "TDate" .

I have created a date table with the columns "date range" and "start of month date". 

Connected them with 1 to many betwwen date column and date range column.

 

I need to count the date if date < start of month date

In excel formula looks like countif(date,"<"&start of month date)


I tried using calculate(count(date),date<start month date) but not really getting a result. Any suggestions>

 

         

 

 

 

Required table visualisation

 

 

 

Need Help

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    remove the join between the tables and decide if this needs to be a measure or if it can be a calculated column. Use SELECTEDVALUE() or ALLEXCEPT()

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    Try this measure

    Count = CALCULATE(COUNT('Table'[Date Range]),FILTER(ALL('Table'),[Date Range]<MAX('Table (2)'[Start Month])))+0

     

     

     

     

     

    Best Regards,

    Stephen Tao

     

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