Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
@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
Proud to be a Super User!
@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
Proud to be a Super User!
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.
@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
Proud to be a Super User!
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
50 |
User | Count |
---|---|
145 | |
109 | |
108 | |
90 | |
64 |