cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dpotta
Resolver I
Resolver I

Help with date measure COUNTIFs in Excel in DAX

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

201/01/2019 00:00:0011/03/2019 00:00:00
301/01/2019 00:00:0011/03/2019 00:00:00
101/01/2019 00:00:0014/01/2019 00:00:00
401/01/2019 00:00:0011/03/2019 00:00:00
501/01/2019 00:00:0011/03/2019 00:00:00
601/01/2019 00:00:0011/03/2019 00:00:00
901/02/2019 00:00:0011/02/2019 00:00:00
701/02/2019 00:00:0011/02/2019 00:00:00
801/02/2019 00:00:0011/02/2019 00:00:00
1001/02/2019 00:00:0011/02/2019 00:00:00
1101/02/2019 00:00:0011/02/2019 00:00:00
1201/02/2019 00:00:0011/02/2019 00:00:00
1301/03/2019 00:00:0011/03/2019 00:00:00
1401/03/2019 00:00:0011/03/2019 00:00:00
1501/03/2019 00:00:0011/03/2019 00:00:00
1601/03/2019 00:00:0011/03/2019 00:00:00
1701/03/2019 00:00:0011/03/2019 00:00:00
2 REPLIES 2
ryan_mayu
Super User II
Super User II

@dpotta 

you can try this

Column = CALCULATE(countrows('Table'),FILTER('Table','DateTable'[Date]>='Table'[StartDAte]&&'DateTable'[Date]<='Table'[CompleteDate]))

1.PNG

 

or

 

measure = CALCULATE(countrows('Table'),FILTER('Table',max('DateTable'[Date])>='Table'[StartDAte]&&max('DateTable'[Date])<='Table'[CompleteDate]))

2.PNG

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




dpotta
Resolver I
Resolver I

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 

 

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors