Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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.
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.
@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.
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.
@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.
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.
@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