Forum Discussion

pawelj795's avatar
pawelj795
Post Prodigy
5 years ago

Count rows with the same date

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...
Data Protection
Need Help
Tips and Tricks
AlB's avatar
AlB
Community Champion
5 years ago

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's avatar
    MartynRamsden
    Solution Sage
    5 years ago

    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!