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, 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]))
  • Bruno_OMB's avatar
    Bruno_OMB
    Regular Visitor
    3 years ago

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