Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
4 years ago
Solved

Total cumulated countrows

Hello Community!

I have an "ASSIGNMENT" table of franchises with their dates of entry, I can know how many franchises entered in a certain month, but I need an accumulated for each month

Captura.JPG

I use a measure for total franchises: Total franchises = COUNTROWS(ASSIGNMENT)

  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Syndicate_Admin ,

    I have created a simple sample, please refer to it to see if it helps you.

    Create two columns first.

    MONTH_ = MONTH(Sheet5[date])
    YEAR = YEAR(Sheet5[date])

    Then create a measure.

    Measure =
CALCULATE (
    SUM ( Sheet5[value] ),
    FILTER (
        ALL ( Sheet5 ),
        Sheet5[MONTH_] <= SELECTEDVALUE ( Sheet5[MONTH_] )
            && Sheet5[YEAR] = SELECTEDVALUE ( Sheet5[YEAR] )
    )
)

     

    If I have misunderstood your meaning, please provide more details with your desired output and pbix file without privacy information.

     

    Best Regards

    Community Support Team _ Polly

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

2 Replies

  • sturlaws's avatar
    sturlaws
    Icon for Resident Rockstar rankResident Rockstar
    4 years ago

    Hi,

    by using this pattern from datpatterns.com, and assuming you have a date-table/dimension, I believe you can write your measure like this:

    Running total = 
VAR LastVisibleDate =
    MAX ( 'Dates'[Month] )
VAR FirstVisibleDate =
    MIN ( 'Dates'[Month] )
VAR LastDateWithSales =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        REMOVEFILTERS ()   
    )
VAR Result =
           IF (
        FirstVisibleDate <= LastDateWithSales,
        CALCULATE (
            sum('Table'[Number of franchises]),
            'Dates'[Month] <= LastVisibleDate 
        )
    )
RETURN
Result

     

    You will of course have to adapt this to your model. I have demonstrated the measure in a very simple mockup report here

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Syndicate_Admin ,

    I have created a simple sample, please refer to it to see if it helps you.

    Create two columns first.

    MONTH_ = MONTH(Sheet5[date])
    YEAR = YEAR(Sheet5[date])

    Then create a measure.

    Measure =
CALCULATE (
    SUM ( Sheet5[value] ),
    FILTER (
        ALL ( Sheet5 ),
        Sheet5[MONTH_] <= SELECTEDVALUE ( Sheet5[MONTH_] )
            && Sheet5[YEAR] = SELECTEDVALUE ( Sheet5[YEAR] )
    )
)

     

    If I have misunderstood your meaning, please provide more details with your desired output and pbix file without privacy information.

     

    Best Regards

    Community Support Team _ Polly

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.