Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculate within the time frame

So here's an example of the dataset I am working on and need to calculate:

The count of tasks if the task falls under the date filter. The date table is connected to the "Start Date".

 

Task NameStart DateEnd Date
A11/1/20162/15/2016
A22/1/20164/16/2016
A33/1/20166/16/2016
A44/1/20168/16/2016
A55/1/2016 
A66/1/2016 
A77/1/2016 
A88/1/201610/17/2017

 

So let's assume I select May 01, 2016 to May 10, 2016 in the date filter, it should populate all the tasks that were ever open during those 10 days. In this case, it should populate A3, A4, A5. So count  = 5. I tried using running total calculation, but it ignores every task created before May 01, 2016. Any other logic can I implement?

General Comment
Need Help

3 Replies

  • v-kelly-msft's avatar
    v-kelly-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    First you need to delete the relationship between the date table and the start date before making the calculation.If you wanna keep the relationship,you need to make a copy of date table to do the calculation.Otherwise the result will be influnced by the date filter.

    After the preparatory work is done,create a measure as below:

     

    Measure = 

SUMX(DISTINCT('Table'[Task Name]),CALCULATE(IF(IF (
            MAX ( 'Table'[Start Date] ) <= MIN ( 'Calendar'[Date] )
                && (
                    MAX ( 'Table'[End Date] ) >= MAX ( 'Calendar'[Date] )
                        || MAX ( 'Table'[End Date] ) = BLANK ()
                ),
            1,
            0
        )=1,IF(MAX('Table'[End Date])=BLANK(),1,2))))

     

    Finally you will see:

    For the related .pbix file,pls click here.

     

    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi v-kelly-msft ,

      I tried it. But it shows as blank when the filter is reset. It should show just simple count of (taskid) in that case because every task falls between those two dates.