topic Re: Count a column containing repeating date with time in DAX Commands and Tips

Count a column containing repeating date with time

patpois — Wed, 20 Dec 2023 18:41:06 GMT

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 ?

Re: Count a column containing repeating date with time

Jayaselvan — Wed, 20 Dec 2023 19:33:51 GMT

Hi,

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

If possible please do the share the sample pbix file?

Re: Count a column containing repeating date with time

patpois — Wed, 20 Dec 2023 19:59:34 GMT

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.

Re: Count a column containing repeating date with time

Anonymous — Thu, 21 Dec 2023 10:38:18 GMT

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

Re: Count a column containing repeating date with time

patpois — Thu, 21 Dec 2023 14:51:19 GMT

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

Re: Count a column containing repeating date with time

patpois — Thu, 21 Dec 2023 14:54:48 GMT

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