Count on table with filter on 2 date fields

[PascalM]

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

[manikumar34]

@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

[PascalM]

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 · Accepted 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

[PascalM]

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