Forum Discussion
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
Perhaps something along the lines of:
Measure = COUNTX(FILTER('Table',MONTH([Date]) = MONTH(TODAY())-1 && [Type]="Open"),[Type])
4 Replies
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?
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.
Perhaps something along the lines of:
Measure = COUNTX(FILTER('Table',MONTH([Date]) = MONTH(TODAY())-1 && [Type]="Open"),[Type])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?
