Calculate within the time frame

[Anonymous]

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 Name	Start Date	End Date
A1	1/1/2016	2/15/2016
A2	2/1/2016	4/16/2016
A3	3/1/2016	6/16/2016
A4	4/1/2016	8/16/2016
A5	5/1/2016
A6	6/1/2016
A7	7/1/2016
A8	8/1/2016	10/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?

[v-kelly-msft]

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]

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.

[Greg_Deckler · Accepted solution]

@Anonymous - See Open Tickets. https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364#M147