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

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's avatar
pawelj795
Post Prodigy
5 years ago

AlB 
Thanks for your help.

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