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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Bruno_OMB
Regular Visitor

Count quantity per month

Hi!
I've been through this since trying to find help with a report I'm developing.
Basically, I have a table of records with columns ID, Start and End, where I want to count the number of active records by date (mm/yy).
As we can see, not all records have End , as they are still active on the current date.
The idea is to visualize the number of active records per month, considering the entry date and (if any) exit date.
Thanks for any help.

Bruno_OMB_0-1671478792324.png

 

1 ACCEPTED SOLUTION
samdthompson
Memorable Member
Memorable Member

Hello, this works pretty well for me. It does need you to make a inactive relation ship between the date and fact table date:end date2022-12-20_13-06-04.png

 

RunningTotal_CountOfOpenRecords =
TOTALYTD(
    COUNTROWS(Test),
    'Date'[Date])-
CALCULATE(
    TOTALYTD(
        [OpenRecordsPerMonth],
        'Date'[Date]),
    USERELATIONSHIP(
        'Date'[Date],
        Test[End]))
// if this is a solution please mark as such. Kudos always appreciated.

View solution in original post

4 REPLIES 4
samdthompson
Memorable Member
Memorable Member

Hello, this works pretty well for me. It does need you to make a inactive relation ship between the date and fact table date:end date2022-12-20_13-06-04.png

 

RunningTotal_CountOfOpenRecords =
TOTALYTD(
    COUNTROWS(Test),
    'Date'[Date])-
CALCULATE(
    TOTALYTD(
        [OpenRecordsPerMonth],
        'Date'[Date]),
    USERELATIONSHIP(
        'Date'[Date],
        Test[End]))
// if this is a solution please mark as such. Kudos always appreciated.

Thanks! They don't necessarily use their formula, but from it I managed to get what I needed in my production environment.

samdthompson
Memorable Member
Memorable Member

Hello, something like this should work:

 

OpenRecords=
CALCULATE(COUNTROWS(Table1),FILTER(Table1,Table1[End]<>""))

Obvs, you'll need to change the table name etc. The relationship to the date table will need to be made to the Start column.

 

Cheers

// if this is a solution please mark as such. Kudos always appreciated.

I tried to follow this line of reasoning, but it does not accumulate monthly.
Example, considering a record with Start:2012-01-01 and End:2012-03-30. I understand that this record should be listed in months 01, 02 and 03 (when, for example, displayed in a Matrix).

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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