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
Using a separate date table, I am trying to identify how many jobs were 'open' on any given date to plot in a bar chart.
Open is defined as where:
the StartDate is >= DateTable[Date]
the CompleteDate <=DateTable[Date]
Can I do this in a single measure, or do I need to write multiple measures?
E,g, if the DateTable[Date] = 01/01/19 the count of OpenJobs would be 6
JobID StartDate CompleteDate
2 | 01/01/2019 00:00:00 | 11/03/2019 00:00:00 |
3 | 01/01/2019 00:00:00 | 11/03/2019 00:00:00 |
1 | 01/01/2019 00:00:00 | 14/01/2019 00:00:00 |
4 | 01/01/2019 00:00:00 | 11/03/2019 00:00:00 |
5 | 01/01/2019 00:00:00 | 11/03/2019 00:00:00 |
6 | 01/01/2019 00:00:00 | 11/03/2019 00:00:00 |
9 | 01/02/2019 00:00:00 | 11/02/2019 00:00:00 |
7 | 01/02/2019 00:00:00 | 11/02/2019 00:00:00 |
8 | 01/02/2019 00:00:00 | 11/02/2019 00:00:00 |
10 | 01/02/2019 00:00:00 | 11/02/2019 00:00:00 |
11 | 01/02/2019 00:00:00 | 11/02/2019 00:00:00 |
12 | 01/02/2019 00:00:00 | 11/02/2019 00:00:00 |
13 | 01/03/2019 00:00:00 | 11/03/2019 00:00:00 |
14 | 01/03/2019 00:00:00 | 11/03/2019 00:00:00 |
15 | 01/03/2019 00:00:00 | 11/03/2019 00:00:00 |
16 | 01/03/2019 00:00:00 | 11/03/2019 00:00:00 |
17 | 01/03/2019 00:00:00 | 11/03/2019 00:00:00 |
you can try this
Column = CALCULATE(countrows('Table'),FILTER('Table','DateTable'[Date]>='Table'[StartDAte]&&'DateTable'[Date]<='Table'[CompleteDate]))
or
measure = CALCULATE(countrows('Table'),FILTER('Table',max('DateTable'[Date])>='Table'[StartDAte]&&max('DateTable'[Date])<='Table'[CompleteDate]))
Proud to be a Super User!
In Excel, I would have done something like this with a date series
= COUNTIFS(jobs[StartDate], "<="&[@Date],jobs[EndDate],">"&[@Date])
I dunno why I cannot get my head round this simple thing
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 |
---|---|
107 | |
98 | |
78 | |
65 | |
53 |
User | Count |
---|---|
144 | |
103 | |
98 | |
85 | |
64 |