Limits on Cumulative Count Measure

Question

Hello,&nbsp;&nbsp;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.&nbsp;CumulativePunchlistClosed = VAR CurrentDate = MAX('DimDate (REF)'[DimDate])RETURN&nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; COUNTROWS('Closed Punch Query'),&nbsp; &nbsp; &nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ALLSELECTED('Closed Punch Query'),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Closed Punch Query'[Workflow - Closing Date] &lt;= CurrentDate&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; )&nbsp;&nbsp;Chat GPT also gave the below but it is even worse than the above.&nbsp;&nbsp;CumClose2 = VAR MinClosingDate = CALCULATE(MIN('Closed Punch Query'[Workflow - Closing Date]), 'Closed Punch Query'[Workflow - Closing Date] &lt;&gt; BLANK())VAR MaxClosingDate = MAX('Reporting Date to Load'[Reporting Date])VAR SelectedDates =&nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; ALL('DimDate (REF)'),&nbsp; &nbsp; &nbsp; &nbsp; 'DimDate (REF)'[DimDate] &gt;= MinClosingDate &amp;&amp; 'DimDate (REF)'[DimDate] &lt;= MaxClosingDate&nbsp; &nbsp; )RETURN&nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; COUNTROWS('Closed Punch Query'),&nbsp; &nbsp; &nbsp; &nbsp; 'Closed Punch Query'[Workflow - Closing Date] &lt;&gt; BLANK(),&nbsp; &nbsp; &nbsp; &nbsp; 'DimDate (REF)'[DimDate] IN SelectedDates&nbsp; &nbsp; )

anonymous · Accepted Answer

Hi&nbsp;bkoeppler&nbsp;,
&nbsp;
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 &gt;= _min_date
            &amp;&amp; CurrentDate &lt;= _max_date,
        CALCULATE (
            COUNTROWS ( 'Closed Punch Query' ),
            FILTER (
                ALLSELECTED ( 'Closed Punch Query' ),
                'Closed Punch Query'[Workflow - Closing Date] &lt;= CurrentDate
                    &amp;&amp; NOT ISBLANK('Closed Punch Query'[Workflow - Closing Date])
            )
        )
    )

&nbsp;
Best Regards,GaoCommunity Support Team
&nbsp;
If there is any post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;&nbsp;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.&nbsp;Thanks a lot!
How to get your questions answered quickly&nbsp;--&nbsp;&nbsp;How to provide sample data in the Power BI Forum

Forum Discussion

Limits on Cumulative Count Measure

2 Replies

Related content

Cumulative Difference from Another measure

Cumulative Measure

Copilot, limitations?

Cumulative Total

Transform measure in cumulative measure