Counting Rows after a specific date

[MatthewFiero]

Hello Power BI Community,

I hope you are all keeping safe.

I have been at this for a few hours and either I am close or I am not approaching my desired output correctly.

What I have is a table of action items on a specific project and 11 of these items have a specific due date. 

What I am looking for is to create a card that lists the items remaining due which I have made as:

Items Remaining = COUNTROWS(FILTER(datatable,datatable[Expected Date of Completion] > TODAY()

I realize this isn't the most robust formula.

Now the tricky part and I am not sure if this is even doable. I would like to now graph the items remaining in a graph, showing that once the final due date (say March 31st) has been reached. Honestly I've gone through so many different itterations of formulas I am not even sure what to give as my try. 

Can anyone give me some clues as to how I can create a linegraph showing that as the date gets closer to May 31s that the remaining actions will begin to decrease ultimately at 0 on the 31st (because there are no action items after this date).

Thank you all for your time and support.

Matthew

[mahoneypat]

Without the ALL(), the evaluaton context of your measure is limited to the Date value at that point on the X axis.  The var part of the new measure harvests that value to be used later.  The ALL() removes the filter on the Date column, so that we can then filter it with a table/filter of Date values > the original Date.  Note that in this context the REMOVEFILTERS() could have been used instead of ALL() which might be more intuitive of what it is doing.

CALCULATE in DAX #03: Remove single column filters - YouTube

Regards,

Pat

[v-xulin-mstf]

Hi @MatthewFiero

Could you provide sample data after removing sensitive information?
Sample data and expected output would help tremendously.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Best Regards,

Link

[mahoneypat · Accepted solution]

Please try a measure expression like this.  You didn't mention that you have a Date table (a good practice), so this assumes you have a single table.  Put it in a line chart with your Expected Date of Completion on the X axis.

Items Remaining =
VAR vMaxDate =
    MAX ( datatable[Expected Date of Completion] )
RETURN
    CALCULATE (
        COUNT ( datatable[Expected Date of Completion] ),
        ALL ( datatable[Expected Date of Completion] ),
        datatable[Expected Date of Completion] > vMaxDate
    )

Pat

[MatthewFiero]

@mahoneypat 

Thank you! The graph works. Are you able to explain why you used the ALL function? I am trying to understand the reasoning behind your code so I can better understand and grow my knowledge.

Thank you,

Matthew