Forum Discussion

patpois's avatar
patpois
Regular Visitor
2 years ago
Solved

Count a column containing repeating date with time

Hi

 

I'm trying to count resolved incidents by month. The data contains date with hour. Note that in the same day, you can have in the same hour multiple entries (2023-12-04 15:15:50 & 2023-12-04 15:19:16). So multiple incidents resolved in the same hour.

 

Excel table example:

 

I'm able to count with this measure below but it doesn't count twice the resolved entries if it's in the same hour. So i'm missing some entries in my count.

Example on december 4th, it only count 1 and it should be 2.

 

Here is the measure that i created but that give me false count :

Incident Resolved Monthly = CALCULATE(DISTINCTCOUNT('BBD Incidents'[Number]),FILTER('BBD Incidents',[Resolved]>=MIN('Date'[Date])&&[Resolved]<=MAX('Date'[Date])))
 
When i use this measure below for a given month, the count is good but i only get one month and not all :
Incident Resolved Monthly = CALCULATE(DISTINCTCOUNT('BBD Incidents'[Number]),FILTER('BBD Incidents',[Resolved]>=DATE(2023,12,01)+TIME(00,00,00)&&[Resolved]<=DATE(2023,12,31)+TIME(23,59,59)))
 
So, how can i fix my measure to be able to get all incidents by month base on date with time that can be in the same hour ?
  • patpois's avatar
    patpois
    2 years ago

    Hi

     

    Here what i did to fix my issue.

    I change the data column from Date/Time to Date in the data value (Edit Query) since I don't care about the time and just want the date count.

     

    So, here is the measure that I created and is functionnal for my needs:

     

    Incident Resolved Monthly = COUNTROWS(FILTER('BBD Incidents',[Resolved]>=MIN('Date'[Date])&&[Resolved]<=MAX('Date'[Date])&&NOT(ISBLANK([Resolved]))))
     
    Thanks you all for your help !
    Have i nice day

5 Replies

  • patpois's avatar
    patpois
    Regular Visitor
    2 years ago

    Hi

     

    Here what i did to fix my issue.

    I change the data column from Date/Time to Date in the data value (Edit Query) since I don't care about the time and just want the date count.

     

    So, here is the measure that I created and is functionnal for my needs:

     

    Incident Resolved Monthly = COUNTROWS(FILTER('BBD Incidents',[Resolved]>=MIN('Date'[Date])&&[Resolved]<=MAX('Date'[Date])&&NOT(ISBLANK([Resolved]))))
     
    Thanks you all for your help !
    Have i nice day
  • Jayaselvan's avatar
    Jayaselvan
    Helper II
    2 years ago

    Hi,

    Can you share the expected output snapshot to get an clear understanding.

    If possible please do the share the sample pbix file?

  • patpois's avatar
    patpois
    Regular Visitor
    2 years ago

    Hi

     

    Adding a more complete table example below:

    The expected result :

    And a graph output :

    So it should not count any blank entries, only entries with resolved date and time. 

    And count all entries where there is a date with time.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi, patpois 

     

    May I ask if this is the expected output you are looking for? Based on your description, I have created many measures to achieve the effect you are looking for. Following picture shows the effect of the display.

    Measures:

    Month =
MONTH ( 'BBD Incidents'[Resolved] )

Incident Resolved Monthly =
VAR _resolved =
    CALCULATE (
        COUNTROWS ( 'BBD Incidents' ),
        FILTER (
            'BBD Incidents',
            [Resolved] >= MIN ( 'BBD Incidents'[Resolved] )
                && [Resolved] <= MAX ( 'BBD Incidents'[Resolved] )
                && ISNUMBER ( [Resolved] )
        )
    )
RETURN
    _resolved

     

    If this does not work, could you please share some sample data without sensitive information and expected output.
    How to provide sample data in the Power BI Forum - Microsoft Fabric Community
    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

    • patpois's avatar
      patpois
      Regular Visitor
      2 years ago

      Hi

       

      I have try the proposed measure but it get an error :