Measure to count tasks left at each time step

[Anonymous]

Hi,

I'm looking for help with a DAX expression that is supposed to return a value that is responsive to the date context. 

My dataset is a list of tasks with a due date. The end goal is to plot over time the number of tasks remaining, based on the due date (I don't consider the actual fulfillment here, this is just a planning tool to see ahead 🙂 ... ). Today, there may be 50 tasks, in a week maybe 40, and we will get to 0 tasks in, say, 2 months. I only look at future due dates. 

A Date dimension table is connected to the factTable[DueDate]. I am thinking of using this datedim for the x-axis of the graph, and the measure for the Values. 

I have a challenge with comparing the due date with each time step. I have tried the following measure: 

Tasks left = CALCULATE(COUNTROWS('All tasks'); 'All tasks'[DueDate] >= DimDate[Date])

... but I get the error message that 

A single value for column ‘Date’ in table ‘DimDate’ cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

.. but in this case I don't want to give the min or max value of the dates: I want it to take any date that I feed it, and give me an answer! ...

I would also like to avoid Calculated Columns because I need it to be responsive to different filters and contexts.

Any thoughts are appreciated on this dark friday night... thanks community!!

[parry2k]

@Anonymous try this measure

Future Tasks = 
CALCULATE ( COUNTROWS ( Tasks ), KEEPFILTERS( 'Calendar'[Date] >= TODAY() ) )

[Anonymous]

Hi @parry2k ,

Thanks for your reply. Unfortunately your measure does not do what I aim at. I get the right value as a total, but when plotted over time, I get the number of tasks DUE each date, not the tasks left ahead. The number I want should always fall over time as due dates are passed.

Under there is a screenshot of what I get with your measure as well as the goal, in green. I managed to illustrate the goal with a summary table and calculated column, but that is not what I want to use due to its inflexibility. 

[parry2k]

@Anonymous try this measure

Tasks Left = 
CALCULATE ( 
    COUNTROWS ( 'Table' ),
    FILTER ( 
        ALL ( 'Calendar'[Date] ), 
        'Calendar'[Date] >= MAX ( 'Calendar'[Date] ) && 
        MAX ( 'Calendar'[Date] ) >= TODAY() ) 
   
 ) 

[Anonymous]

Hi @parry2k , 

It looks like your suggestion does not work. I applied your expression for my case (so 'Table' = 'All tasks' and 'Calendar' = 'DimDate'), and again got a graph jumping up and down, and values where not right. 

Could you maybe explain your expression so that I can see if it can be adapted? 

I would also be useful to see what visualisation you get with your expression- maybe it's something else that leads us to get different results.

In addition, I expected that the expression would need to contain the argument "DueDate" from the fact table, since that is the important factor on which I want to calculate. Why do you just include the date dimension table?