cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
shaunguyver Member
Member

Count values occurring between two dates

I have a column, 'Date', which gives a range of dates across 2018.

 

I have a second column, 'Type', which gives the type of event associated with that date. The values are either 'Open' or 'Closed'.

 

I want to create a measure that calculates how many 'Open' events occured in the past month, from today.

 

I have tried to create this measure but have had no luck. Does anyone have any ideas?

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Count values occurring between two dates

Perhaps something along the lines of:

 

Measure = 
COUNTX(FILTER('Table',MONTH([Date]) = MONTH(TODAY())-1 && [Type]="Open"),[Type])

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

Proud to be a Datanaut!


4 REPLIES 4
Thim Regular Visitor
Regular Visitor

Re: Count values occurring between two dates

Just to be sure.

 

Today is the 30th October 2018.

 

Does that mean you want to see the numbers of open and closed for the period 1st of september til today or from the1st October to today?

Thim Regular Visitor
Regular Visitor

Re: Count values occurring between two dates

Instead of calculating, you could make PowerBi count the types.

And then just filter on the date.

Here is a visual with the date, and without the date appearing.

 

calc.PNG

Super User
Super User

Re: Count values occurring between two dates

Perhaps something along the lines of:

 

Measure = 
COUNTX(FILTER('Table',MONTH([Date]) = MONTH(TODAY())-1 && [Type]="Open"),[Type])

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

Proud to be a Datanaut!


shaunguyver Member
Member

Re: Count values occurring between two dates

Thanks Greg! That worked perfectly.

 

One more thing - I need a similar DAX formula that does the exact same thing, but apart from the timeframe being last 30 days, it is since a fixed date: ie, 01/01/2018. Is that possible?