Forum Discussion
Count on table with filter on 2 date fields
Hi,
I have a table containing Jobs, and a table with the Tasks that should be performed during specific periods for these jobs:
JobId | Name |
101 | Job A |
102 | Job B |
103 | Job C |
104 | Job C |
TaskId | StartDate | EndDate | JobId |
1 | 12/15/2019 | 1/31/2020 | 101 |
2 | 2/1/2020 | 3/15/2020 | 102 |
3 | 3/1/2020 | 3/31/2020 | 103 |
4 | 4/15/2020 | 4/30/2020 | 102 |
5 | 6/1/2020 | 7/15/2020 | 104 |
Columns StartDate and EndDate are linked to a Calendar table.
I need a measure to count the number of jobs that are started and finished within a particular period (e.g. 1/1/2020 – 6/30/2020).
In SQL this would be rather straightforward:
SELECT COUNT(JobId) FROM
(
SELECT JobId, MIN(StartDate), MAX(EndDate)
FROM Jobs
GROUP BY JobId
HAVING MIN(StartDate) >= ‘20200101’ AND MAX(EndDate) <= ‘20200630’
)
As I’m new to PowerBI, I cannot find a way to create this formula I DAX. How can I apply the date filter in the linked Calendar table (on 2 different date fields)? Any help highly appreciated!
Pascal
PascalM ,
If you have a relationshp between those two tables, that won't give you any problem. And as you have given hardcoded value you don't need to use MIN or MAX.
If you want to change the date values dynamically when you apply filters create variables with SELCTEDVALUE and use in the measure where date =.
Regards,
Manikumar
4 Replies
- manikumar34
Solution Sage
PascalM ,
In DAX also it is straight forward. Something like below
Calculate(COUNT(JobId) ,FILTER( table,MIN(table[StartDate]) >= ‘20200101’ AND MAX(table[EndDate) <= ‘20200630’)
assuming the same format for dates as you have given.
Regards,
Manikumar
Hi Manikumar,
Thanks for the quick reply. However, as my date filter needs to be applied via the Calendar table where the period is determined using a CurWeekOffset column that is being determined with data coming from another table, I cannot hardcode the dates in the formula. What should the formula look like when the filter needs to be applied via the Calendar table?
Thanks
Pascal
Also, I don't think that in the formula you suggest, the MIN and MAX work as expected. MIN and MAX should only be applied on rows for the same JobId. I think that in your formula it looks at all JobIds. So first I need to transform my original Task table with only 1 row per JobId, and corresponding MIN and MAX dates. Then I need to count which of these Tasks fall within the specified date range.
- manikumar34
PascalM ,
If you have a relationshp between those two tables, that won't give you any problem. And as you have given hardcoded value you don't need to use MIN or MAX.
If you want to change the date values dynamically when you apply filters create variables with SELCTEDVALUE and use in the measure where date =.
Regards,
Manikumar
