Count rows with the same date

[pawelj795]

Hi,
I have data like below.

Now, I want to count how many journals were closed and how many journals were opened on a particular date.
How to do this?

Sample file:
https://drive.google.com/file/d/1EEij_gV3NohDy1YXKrpSskoRlUQbmz19/view?usp=sharing

[amitchandak]

@pawelj795 , Refer to my blog on a similar topic. You need to create a date table and join both dates to it. Active/inactive relation will be created. You can use userelation to activate one

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

https://www.youtube.com/watch?v=e6Y-l_JtCq4&t=304s

[pawelj795]

@amitchandak 
I've done everything you wrote in your article, but it still doesn't work. 
Could you tell me what's wrong?

Below my file:
https://drive.google.com/file/d/1cZWBHS07cqX62m_6vnR5X--Op0QceGyT/view?usp=sharing

[AlB]

Hi @pawelj795 Get rid of the relationships and create these measures:

Count of Closed Journals V2 =
CALCULATE (
    DISTINCTCOUNT ( Sheet1[Journal number] ),
    FILTER (
        ALL ( Sheet1[Closed Date] ),
        Sheet1[Closed Date] <= MAX ( 'Date'[Date] )
            && NOT ISBLANK ( Sheet1[Closed Date] )
    )
)

Count of created Journals V2 =
CALCULATE (
    DISTINCTCOUNT ( Sheet1[Journal number] ),
    FILTER (
        ALL ( Sheet1[Created date] ),
        Sheet1[Created date] <= MAX ( 'Date'[Date] )
    )
)

See it all at work in the attached file.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[pawelj795]

@AlB 
Thanks for your help.

Unfortunately, your measure is sum journals cumulatively and this isn't my case.

[pawelj795]

up

[AlB]

@pawelj795 

Ah, you want the openings and closing only on that specific date?  Easy, we just need an = instead of a <= in the filtering then:

Count of Closed Journals V2 =
CALCULATE (
    DISTINCTCOUNT ( Sheet1[Journal number] ),
    FILTER (
        ALL ( Sheet1[Closed Date] ),
        Sheet1[Closed Date] = MAX ( 'Date'[Date] )
            && NOT ISBLANK ( Sheet1[Closed Date] )
    )
)

Count of created Journals V2 =
CALCULATE (
    DISTINCTCOUNT ( Sheet1[Journal number] ),
    FILTER (
        ALL ( Sheet1[Created date] ),
        Sheet1[Created date] = MAX ( 'Date'[Date] )
    )
)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[MartynRamsden]

Czesc @pawelj795

The problem here is that the 'Created Date' and 'Closed Date' columns in table 'Sheet1' include a time element and are set to the 'Date/Time' data type.

It is important to remember that when you create a relationship with a date table, the column on the many side should only include the date (no time element) and be set to the 'Date' data type.

If you cannot remove the time element in the source data, you can do so in Power Query.

You can then create your relationships:

• Date[Date] (1--->*) Sheet1[Date Created] (Active)
• Date[Date] (1--->*) Sheet1[Closed Date] (Inactive)

Once you've done this, and your relationships work properly, you can create 2 very simple measures

to give you the desired results:

Journals Opened = COUNTROWS(Sheet1)

Journals Closed = 
CALCULATE ( 
    COUNTROWS( Sheet1 ), 
    USERELATIONSHIP('Date'[Date], Sheet1[ClosedDate] )
)

I hope that helps!

[rajend12]

@pawelj795 

I have came across this and have a solution for you...

You can unpivot the Created date and Closed date columns in Query Editor. Please refer this link below:

https://www.newtechdojo.com/unpivot-columns-in-power-bi/

Try and please do reply for any more clarifications. Cheers !!!!!!

Deepan