Total cumulated countrows

[Syndicate_Admin]

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

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

[v-rongtiep-msft · Accepted solution]

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.

[sturlaws]

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