Forum Discussion

MatthewFiero's avatar
MatthewFiero
Helper I
5 years ago
Solved

Counting Rows after a specific date

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
Need Help
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    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

     

4 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    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's avatar
      MatthewFiero
      Helper I
      5 years ago

      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

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    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