Number of ids that are fall under certain date range

[bharukc]

Below is the sample table, I need to calculate

count of ids that occured between 1/1/2012-1/1/2013, 4/1/2012-4/1/2013, 7/1/2012-7/1/2013 and so on. How many Ids were due from the start of the quarter to a year from the start of quarter. IDs that fall under due between 1/1/2012-1/1/2013 will also be in 4/1/2012-4/1/2013, as they share common dates. 

Id	due date
1	1/3/2012
2	5/3/2012
3	8/3/2012
4	9/2/2012
5	1/4/2013
6	3/5/2013
7	4/9/2013
8	8/9/2013
9	9/10/2013

[v-zhangti]

Hi, @bharukc 

Please check the following methods.

Measure:

2012/1/1-2013/1/1 = 
CALCULATE(COUNT('Table'[Id]),FILTER(ALL('Table'),[due date]>=DATE(2012,1,1)&&[due date]<=DATE(2013,1,1)))

2012/4/1-2013/4/1 = 
CALCULATE(COUNT('Table'[Id]),FILTER(ALL('Table'),[due date]>=DATE(2012,4,1)&&[due date]<=DATE(2013,4,1)))

2012/4/1-2013/4/1 = 
CALCULATE(COUNT('Table'[Id]),FILTER(ALL('Table'),[due date]>=DATE(2012,4,1)&&[due date]<=DATE(2013,4,1)))

Is this the result you were expecting?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bharukc]

@v-zhangti , yes the result is correct but I dont want to create measures for each date, I would rather have it as on measure so that I can create a chart with it. 

[v-zhangti · Accepted solution]

Hi, @bharukc 

It can be used only as a measure.

Create a new date table.

Date = CALENDAR(DATE(2012,1,1),DATE(2013,12,31))

Measure:

Measure = 
CALCULATE(COUNT('Table'[Id]),FILTER(ALL('Table'),[due date]>=MIN('Date'[Date])&&[due date]<=MAX('Date'[Date])))

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@bharukc , these are overlapping year ranges. You want to check they are on the start date of QTR or they are in a qtr or in these overlapping ranges

The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.

[bharukc]

Hi @amitchandak , there will be overlapping time among these dates. I need to be able to count the id that fall under each date range multiple times. For eg, there might be 6 ids that fall under 1/1/2012-1/1/2013 range and 8 ids fall under 4/1/12-4/1/13. Out of these 8, 4 would fall under both 1/1/2012-1/1/2013 and 4/1/12-4/1/13 range. I need to be able to count ids that fall under that date range regardless if they are being counted on previous rows.

[amitchandak]

@bharukc , On of the way is

countrows(filter(Table, ( Table[due date] >= date(2012,1,1)  && Table[due date] < date(2013,1,1) )

|| ( Table[due date] >= date(2012,4,1)  && Table[due date] < date(2013,4,1) ) ))

If there date range table we need different logic