Forum Discussion

Bruno_OMB's avatar
Bruno_OMB
Regular Visitor
3 years ago
Solved

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 re...
Need Help
  • samdthompson's avatar
    samdthompson
    3 years ago

    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 date

     

    RunningTotal_CountOfOpenRecords =
    TOTALYTD(
        COUNTROWS(Test),
        'Date'[Date])-
    CALCULATE(
        TOTALYTD(
            [OpenRecordsPerMonth],
            'Date'[Date]),
        USERELATIONSHIP(
            'Date'[Date],
            Test[End]))
samdthompson's avatar
samdthompson
Memorable Member
3 years ago

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

Bruno_OMB's avatar
Bruno_OMB
Regular Visitor
3 years ago

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).