Limits on Cumulative Count Measure

[bkoeppler]

Hello, 

I've written a cumulative count measure that counts the rows up based off a column that is either blank if the task has not been closed or has a closing date in it if it has been closed. I cannot find a way to only take the cumulative count of rows where there is a date in the column and not add the row if it is blank. Additional to this, I only want the graph to start from when the first closing date is entered and stop on the graph at today's date. Currently it shows data for every date that I have in my Date Dimension table, which is flat lining the line graph out way too far.

CumulativePunchlistClosed =

VAR CurrentDate = MAX('DimDate (REF)'[DimDate])

RETURN

    CALCULATE(

        COUNTROWS('Closed Punch Query'),

        FILTER(

            ALLSELECTED('Closed Punch Query'),

            'Closed Punch Query'[Workflow - Closing Date] <= CurrentDate

        )

    )

Chat GPT also gave the below but it is even worse than the above. 

CumClose2 =

VAR MinClosingDate = CALCULATE(MIN('Closed Punch Query'[Workflow - Closing Date]), 'Closed Punch Query'[Workflow - Closing Date] <> BLANK())

VAR MaxClosingDate = MAX('Reporting Date to Load'[Reporting Date])

VAR SelectedDates =

    FILTER(

        ALL('DimDate (REF)'),

        'DimDate (REF)'[DimDate] >= MinClosingDate && 'DimDate (REF)'[DimDate] <= MaxClosingDate

    )

RETURN

    CALCULATE(

        COUNTROWS('Closed Punch Query'),

        'Closed Punch Query'[Workflow - Closing Date] <> BLANK(),

        'DimDate (REF)'[DimDate] IN SelectedDates

    )

[v-cgao-msft · Accepted solution]

Hi @bkoeppler ,

Please try:

CumulativePunchlistClosed = 
VAR _min_date =
    MIN ( 'Closed Punch Query'[Workflow - Closing Date] )
VAR _max_date =
    TODAY ()
VAR CurrentDate =
    MAX ( 'DimDate (REF)'[DimDate] )
RETURN
    IF (
        CurrentDate >= _min_date
            && CurrentDate <= _max_date,
        CALCULATE (
            COUNTROWS ( 'Closed Punch Query' ),
            FILTER (
                ALLSELECTED ( 'Closed Punch Query' ),
                'Closed Punch Query'[Workflow - Closing Date] <= CurrentDate
                    && NOT ISBLANK('Closed Punch Query'[Workflow - Closing Date])
            )
        )
    )

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[bkoeppler]

That's Great! Thanks for your help Gao - worked perfectly.