Forum Discussion

WLFRD's avatar
WLFRD
Helper III
3 years ago
Solved

Count rows with different dates

Hello,

 

can someone help me with counting rows with different dates (Date1 and Date2) in it?

 

DateDate1Date2
04-Nov-2204-Nov-2204-Nov-22
04-Nov-2204-Nov-2204-Nov-22
04-Nov-2204-Nov-2204-Nov-22
04-Nov-2204-Nov-2214-Nov-22
04-Nov-2204-Nov-2204-Nov-22
04-Nov-2204-Nov-2204-Nov-22
04-Nov-2204-Nov-2204-Nov-22
04-Nov-2204-Nov-2204-Nov-22
04-Nov-2204-Nov-2231-Dec-99
04-Nov-2204-Nov-2214-Nov-22
04-Nov-2204-Nov-2210-Nov-22
04-Nov-2204-Nov-2204-Nov-22
04-Nov-2204-Nov-2204-Nov-22
04-Nov-2204-Nov-2204-Nov-22
04-Nov-2204-Nov-2204-Nov-22
04-Nov-2204-Nov-2231-Dec-99
04-Nov-2204-Nov-2231-Dec-99
04-Nov-2204-Nov-2211-Nov-22
04-Nov-2204-Nov-2204-Nov-22
04-Nov-2204-Nov-2204-Nov-22
04-Nov-2204-Nov-2204-Nov-22
04-Nov-2204-Nov-2204-Nov-22
04-Nov-2204-Nov-2209-Nov-22
04-Nov-2204-Nov-2207-Nov-22
04-Nov-2204-Nov-2209-Nov-22
04-Nov-2204-Nov-2231-Dec-99
04-Nov-2204-Nov-2214-Nov-22
04-Nov-2204-Nov-2209-Nov-22
04-Nov-2204-Nov-2209-Nov-22
04-Nov-2204-Nov-2210-Nov-22
04-Nov-2204-Nov-2231-Dec-99
04-Nov-2204-Nov-2204-Nov-22
04-Nov-2204-Nov-2207-Nov-22

 

I would like to count the yellow marked rows. These have different dates (Date1 and Date2) other than 31-DEC-99. This date must not be included in the count of rows.

Expected result for 04-NOV-22 is 12.

 

Thanks in advance.

 

Regards

Need Help
  • m_alireza's avatar
    m_alireza
    3 years ago

    WLFRD ,

    Should still work. 

    If you created a table with the "Date" column and the measure, you should get a count per date, for example:

    And if you want the measure to return 0 when there is 0 rows returned (like on nov 6th in my example) then you can add a "+0" at the end of the meaure.

3 Replies

  • m_alireza's avatar
    m_alireza
    Solution Specialist
    3 years ago

    Hi WLFRD ,

    Try this measure: 

    Measure = CALCULATE( COUNTROWS('Table'),'Table'[Date1]<>'Table'[Date2], 'Table'[Date2]<> DATE(1999, 12 , 31))
    • WLFRD's avatar
      WLFRD
      Helper III
      3 years ago

      Thanks for your reply. What would be the measure if there are more dates than just the 11/04/2022 which are in the given example? 

      It should count the rows per day and return a single number per date for which Date2 and Date1 are not the same and exclude date2 when it is 31DEC99.

  • m_alireza's avatar
    m_alireza
    Solution Specialist
    3 years ago

    WLFRD ,

    Should still work. 

    If you created a table with the "Date" column and the measure, you should get a count per date, for example:

    And if you want the measure to return 0 when there is 0 rows returned (like on nov 6th in my example) then you can add a "+0" at the end of the meaure.