Running Total - Do Not Want to See past Today's Date

[neil37]

Hello, 

I use the following code to calculate the running total of counts:

Count of ucr_code running total in Month =
CALCULATE(
COUNTA('lwchrg (2)'[code]),
FILTER(
CALCULATETABLE(
SUMMARIZE(
'lwmain (2)',
'lwmain (2)'[date_occu].[MonthNo],
'lwmain (2)'[date_occu].[Month]
),
ALLSELECTED('lwmain (2)')
),
ISONORAFTER(
'lwmain (2)'[date_occu].[MonthNo], MAX('lwmain (2)'[date_occu].[MonthNo]), DESC,
'lwmain (2)'[date_occu].[Month], MAX('lwmain (2)'[date_occu].[Month]), DESC

BUT - I do not want it to count past todays Date. Is there a filter or add-in code to this current measure to prevent it from counting past today?

I do not want the code to calculate past today's date (i.e., March, April, May, etc. of 2022.).

Thank you!

[Anonymous · Accepted solution]

Hi @neil37 ,

Please have a try.

Modify the formula before.

Measure =
VAR result =
    CALCULATE (
        COUNTA ( 'lwchrg (2)'[code] ),
        FILTER (
            ALLSELECTED ( 'lwmain (2)' ),
            'lwmain (2)'[date_occu].[MonthNo] <= MAX ( 'lwmain (2)'[date_occu].[MonthNo] )
                && 'lwmain (2)'[date_occu].[Year] = MAX ( 'lwmain (2)'[date_occu].[Year] )
                && 'lwmain (2)'[date_occu] <= TODAY ()
        )
    )
RETURN
    IF (
        SELECTEDVALUE ( 'lwmain (2)'[date_occu].[MonthNo] ) > MONTH ( TODAY () )
            && SELECTEDVALUE ( 'lwmain (2)'[date_occu].[Year] ) >= YEAR ( TODAY () ),
        BLANK (),
        result
)

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.

[neil37]

@Anonymous  Thank you!!! Greatly appreciate you're diligence, intelligence, and patience

[Anonymous]

Hi @neil37 ,

Hi @neil37 ,

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

Create a measure.

Measure =
CALCULATE (
    COUNTA ( 'lwchrg (2)'[code] ),
    FILTER (
        ALLSELECTED ( 'lwmain (2)' ),
        'lwmain (2)'[date_occu].[MonthNo] <= MAX ( 'lwmain (2)'[date_occu].[MonthNo] )
            && 'lwmain (2)'[date_occu].[Year] = MAX ( 'lwmain (2)'[date_occu].[Year] )
            && 'lwmain (2)'[date_occu] <= TODAY ()
    )
)

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

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.

[neil37]

Hi @Anonymous , 

Thank you for your support and response. 

Unfortunately, we have gotten to the point where the running total is calculated - the only problem is we want that running total to completely stop counting for any date past Today.

So in your table example, we'd like the table to show a running count that does not extend past the current month:

Month	2021	2022
Jan.	2	1
Feb.	3	2
March	6
April	7
May	8
June	9
July	10
August	11

In the image below, we'd like the line to stop at the current month:

We've tried calendar table filters to not extend past todays date and nothing seems to stop that running total from continuing to count throughout the current year. 

Thank you again for your help.

[amitchandak]

@neil37 , try like

Count of ucr_code running total in Month =
CALCULATE(
COUNTA('lwchrg (2)'[code]),
FILTER(
CALCULATETABLE(
SUMMARIZE(
'lwmain (2)',
'lwmain (2)'[date_occu].[MonthNo],
'lwmain (2)'[date_occu].[Month]
),
ALLSELECTED('lwmain (2)')
),
ISONORAFTER(
'lwmain (2)'[date_occu].[MonthNo], MAX('lwmain (2)'[date_occu].[MonthNo]), DESC,
'lwmain (2)'[date_occu].[Month], MAX('lwmain (2)'[date_occu].[Month]), DESC
), 'lwmain (2)'[date_occu] <=today()
))

[neil37]

@amitchandak  Thank you for your quick response!

Unfortunately, I am getting an error that says " there are too many arguements were passed to the FILTER function. The max arguement count for the function is 2."