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
you can try this
Column = CALCULATE(countrows('Table'),FILTER('Table','DateTable'[Date]>='Table'[StartDAte]&&'DateTable'[Date]<='Table'[CompleteDate]))
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
Check out new user group experience and if you are a leader please create your group!
Check out how to claim yours today!
Test your skills now with the Cloud Skills Challenge.