Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
patpois
Regular Visitor

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:

patpois_0-1703096780471.png

 

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 ?
1 ACCEPTED SOLUTION
patpois
Regular Visitor

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

View solution in original post

5 REPLIES 5
patpois
Regular Visitor

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
v-yaningy-msft
Community Support
Community Support

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.

vyaningymsft_1-1703154991383.png

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

Hi

 

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

patpois_0-1703170190719.png

 

patpois
Regular Visitor

Hi

 

Adding a more complete table example below:

patpois_0-1703101963682.png

The expected result :

patpois_1-1703102025315.png

And a graph output :

patpois_2-1703102275421.png

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.

Jayaselvan
Helper II
Helper II

Hi,

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

If possible please do the share the sample pbix file?

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors