The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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 |
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.
Solved! Go to Solution.
Hi @bharukc ,
Based on your description, I have firstly customed a calendar table:
Then use STARTOFQUARTER() to get all this year and next year 's "the start of the quarter to a year"
New Table =
ADDCOLUMNS (
DISTINCT (
SELECTCOLUMNS ( 'Dates', "start", STARTOFQUARTER ( 'Dates'[Date] ) )
),
"end", DATE ( YEAR ( [start] ) + 1, MONTH ( [start] ), DAY ( [start] ) )
)
Now we could count rows in each period:
Count = CALCULATE(COUNTROWS('Table'),FILTER('Table',[due date]>=MAX('New Table'[start]) && [due date]<=MAX('New Table'[end])))
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @bharukc ,
Based on your description, I have firstly customed a calendar table:
Then use STARTOFQUARTER() to get all this year and next year 's "the start of the quarter to a year"
New Table =
ADDCOLUMNS (
DISTINCT (
SELECTCOLUMNS ( 'Dates', "start", STARTOFQUARTER ( 'Dates'[Date] ) )
),
"end", DATE ( YEAR ( [start] ) + 1, MONTH ( [start] ), DAY ( [start] ) )
)
Now we could count rows in each period:
Count = CALCULATE(COUNTROWS('Table'),FILTER('Table',[due date]>=MAX('New Table'[start]) && [due date]<=MAX('New Table'[end])))
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
57 | |
32 | |
20 | |
17 | |
15 |
User | Count |
---|---|
49 | |
33 | |
30 | |
23 | |
20 |